Concatenation of ALPHA and NUMERIC cells?

[Guest]

I have a need to insert a"B" in front of each part number in a list of
product information. Many of the product numbers are numeric. Obviously the
"B" is alpha. They will not concatenate using the normal =CONCATENATE(A2,B2)
function. Is there a work around? Is there a way to convert the numeric to
alpha? I don't really need it to be a numeric field...

Thanks

 

[Dave Peterson]

Does the =concatenate(a2,b2) (or =a2&b2) fail because you lose leading 0's or
other custom formatting?

If yes, you could use:
=$a$2 & text(b2,"00000")
or
="B" & text(b2,"00000.00000")

 

[Gord Dibben]

You should have no problem with this function yet you say they will not
concatenate.

Assuming you have the numerics in column B

In column adjacent to your numerics enter

="B" & B2

Double-click to copy down.

Which is the same as having B in A2 and using this formula in C2

=CONCATENATE($A$2,B2) and double-click to copy down.


Gord Dibben MS Excel MVP