I have a situation where there are 6 colums with data in each row:

V, 02, BA,1,3,R

When I concatenate this, the result drops the 0 preceding the 2 and gives
the result V2BA13R when it should be V02BA13R. How do I do this?

I cannot change the 0 to a O as part of the process. It must remain as 0. I
attempted to find/replace the 0 with '0 to make it text but that didn't work
either.

Anyone have any ideas?

You could use TEXT for col B's data,
eg in G1:
=A1&TEXT(B1,"00")&C1&D1&E1&F1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"markmcd" wrote:
I have a situation where there are 6 colums with data in each row:

V, 02, BA,1,3,R

When I concatenate this, the result drops the 0 preceding the 2 and gives
the result V2BA13R when it should be V02BA13R. How do I do this?

I cannot change the 0 to a O as part of the process. It must remain as 0. I
attempted to find/replace the 0 with '0 to make it text but that didn't work
either.

Anyone have any ideas?

Assuming they are in A1:F1
=A1&TEXT(B1,"00")&C1&D1&E1&F1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"markmcd" wrote in message
...
I have a situation where there are 6 colums with data in each row:

V, 02, BA,1,3,R

When I concatenate this, the result drops the 0 preceding the 2 and gives
the result V2BA13R when it should be V02BA13R. How do I do this?

I cannot change the 0 to a O as part of the process. It must remain as 0.
I
attempted to find/replace the 0 with '0 to make it text but that didn't
work
either.

Anyone have any ideas?

If the 02 is not currently text, then why hasn't the 0 been dropped before the
concatenation?

Presumably the 2 is a number formatted as custom 00

If it is currently text, it should hold the 0 through your concatenation.


Gord Dibben MS Excel MVP

On Mon, 10 Dec 2007 15:38:00 -0800, markmcd
wrote:

I have a situation where there are 6 colums with data in each row:

V, 02, BA,1,3,R

When I concatenate this, the result drops the 0 preceding the 2 and gives
the result V2BA13R when it should be V02BA13R. How do I do this?

I cannot change the 0 to a O as part of the process. It must remain as 0. I
attempted to find/replace the 0 with '0 to make it text but that didn't work
either.

Anyone have any ideas?