Numeric to character

[Bob]

I have 2 cols "Ref1"(col a) and "Ref2"col b.

"Ref1" is up to 6 digits long and "Ref2" is up to 8 digits long. I now
need to convert these 2 columns to character. The character field does
need to be 6 or 8 digits long, so in some cases ie ref1 may only be
"456" while it needs to end up as 000456 in character format and to
appear in a "new" col k. and similary with ref2 which needs to end as
8 characters long in col L.

I believe there is a simply formula that will give me this result and
i can then "drag" it down the column length.(I need to change about
6000 records in total)

Could somebody help with request please.

With thanks

I

 

[Otto Moehrbach]

In the 6 digit column, select all the cells that you want and format them as
Custom 000000. Do the same with the 8 digit numbers, 00000000. Note that
the result is a number. HTH Otto

 

[Lars-Åke Aspelin]

I have 2 cols "Ref1"(col a) and "Ref2"col b.

"Ref1" is up to 6 digits long and "Ref2" is up to 8 digits long. I now
need to convert these 2 columns to character. The character field does
need to be 6 or 8 digits long, so in some cases ie ref1 may only be
"456" while it needs to end up as 000456 in character format and to
appear in a "new" col k. and similary with ref2 which needs to end as
8 characters long in col L.

I believe there is a simply formula that will give me this result and
i can then "drag" it down the column length.(I need to change about
6000 records in total)

Could somebody help with request please.

With thanks

I

If you want the results to be text you may try this:

In cell K2: =RIGHT("000000"&A2,6)
In cell L2: =RIGHT("00000000"&B2,8)

Hope this helps / Lars-Åke

 

[Gord Dibben]

=TEXT(A1,"000000") in K1

=TEXT(B1, "00000000") in L1

Drag/copy down.


Gord Dibben MS Excel MVP