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

[Basher Bates]

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]

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