How to copy a number into a text cell, keeping leading zeros?
 

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?

How to copy a number into a text cell, keeping leading zeros?
 

Hi,

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

Cheers
Andy

Basher Bates wrote:
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?

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

How to copy a number into a text cell, keeping leading zeros?
 

just testing how to reply.
--
cityboyjerry


"Basher Bates" wrote:

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?