working with columns with custome formatting ?

T

Tim

I have a couple columns of numbers that needed zeros added to them as
follows:

A B C
145 29 812
125 8 4

need to be formatted as

145 29 812
125 08 004

Using format each column using Custom. Format the 2 digit column as 00, and
the 3
digit column as 000 the columns work fine.

But...I then need to use the columns in a combinations formula =A2&B2&C2 to
have a resulting 8 digit number...but the 'custom' 0's do not convert...I
get a number like 12584 instead of the needed 12508004 ?

Anyway around this issue ?

Thanks, Tim
 
J

JE McGimpsey

One way:

=TEXT(A1,"000") & TEXT(B1,"00") & TEXT(C1,"000")

another:

=TEXT(A1*100000 + B1*1000 + C1, "00000000")
 
G

Gord Dibben

As you have found, formatting for display does not change the underlying value
of the data.

In D2 enter =TEXT(A2,"000") & TEXT(B2,"00") & TEXT(C2,"000")

Copy D2 and paste special>values.

The data will be in text format and calculations can't take place.

To overcome that..................

Select D2 and Data>Text to Columns>Next>Next>Column Data Format>General and
Finish.


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top