Handling leading zeros informulas

[shielwolf]

I have 3 columns that have data that looks like;

1) 1001, 2) 06, 3) 02.

i am trying to combine them with a formula but excel drops the zeros i
the formula.

The end result I am looking for is 1001_06_02. Currently I ge
1001_6_2

Thanks for the help

 

[Norman Harker]

Hi Shielwolf!

Try:
=A1&"_"&TEXT(B1,"00")&"_"&TEXT(C1,"00")

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Captain]

I have 3 columns that have data that looks like;

1) 1001, 2) 06, 3) 02.

i am trying to combine them with a formula but excel drops the zeros in
the formula.

The end result I am looking for is 1001_06_02. Currently I get
1001_6_2

Thanks for the help!

In excel are the figures visible to you with the leading zeros?
They will be, only if the cells are formatted as 'text'
So, first, ensure that the cells are formatted to receive 'text',
second, see if the leading zeroes are visible and then now do the
concatenation. This should see you thru'
HTH
Ravi

 

[David McRitchie]

In VBA you could use .text instead of .value

In Excel it sounds like those are numbers with a
cell formatting of 2 digits i.e. 00
If they are numbers then perhaps you can use
=TEXT(A1,"0000") & "_" & TEXT(B1,"00") & "_" & TEXT(C1,"00)
The results will be text but they you would have the
results as text anyway.