software

In order to standardise an index reference for a LOOKUP table, I need to
CONCATENATE cells from three columns, which could be (typically) as follows:-
B, 010, 030 or even A, 000, 040. This would give a concatenation of
B010030, etc..

The data has been standardised into this form to allow sorting within a
reference table and I then need to extract cells within that table for use in
my spreadsheet, using the LOOKUP functions.
This works fine if I input '000' as text but if I try to convert a 3-digit
numerical cell to text the leading zeros are dropped, resulting in B1030 or
A040, using the above examples.

Does anybody have a workaround for this problem?

Hi,

You could use the TEXT() function to maintain leading zeros.
=TEXT(6,quot;000quot;)
would give you 006

Cheers
Andy

Basher Bates wrote:
gt; In order to standardise an index reference for a LOOKUP table, I need to
gt; CONCATENATE cells from three columns, which could be (typically) as follows:-
gt; B, 010, 030 or even A, 000, 040. This would give a concatenation of
gt; B010030, etc..
gt;
gt; The data has been standardised into this form to allow sorting within a
gt; reference table and I then need to extract cells within that table for use in
gt; my spreadsheet, using the LOOKUP functions.
gt; This works fine if I input '000' as text but if I try to convert a 3-digit
gt; numerical cell to text the leading zeros are dropped, resulting in B1030 or
gt; A040, using the above examples.
gt;
gt; Does anybody have a workaround for this problem?

--

Andy Pope, Microsoft MVP - Excel
www.andypope.info

just testing how to reply.
--
cityboyjerryquot;Basher Batesquot; wrote:

gt; In order to standardise an index reference for a LOOKUP table, I need to
gt; CONCATENATE cells from three columns, which could be (typically) as follows:-
gt; B, 010, 030 or even A, 000, 040. This would give a concatenation of
gt; B010030, etc..
gt;
gt; The data has been standardised into this form to allow sorting within a
gt; reference table and I then need to extract cells within that table for use in
gt; my spreadsheet, using the LOOKUP functions.
gt; This works fine if I input '000' as text but if I try to convert a 3-digit
gt; numerical cell to text the leading zeros are dropped, resulting in B1030 or
gt; A040, using the above examples.
gt;
gt; Does anybody have a workaround for this problem?