CONCATENATE without Leading zero in Excel

[gabber4858]

I am trying to concatenate cells in excel, but without the leading zero. I set a custom format in the individual cells to omit the leading zero, but it comes back in my formula.

Example: 133 .09 A .01 becomes 1330.09A0.01 when I really want it to be 133.09A.01

Is this possible?

 

[macropod]

Hi gabber4858,

What are the actual cell data - it's impossible to tell from your post what parts of the strings are in which cells.


Cheers
[MS MVP - Word]

 

[GlennUK]

I'd guess something similar to:

=G4&TEXT(H4,"#.00")&I4&TEXT(J4,"#.00")

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[lionelhill]

If 133 and .09 are stored as numbers, and the second is always starting with a decimal (i.e. <= 1.0) you could just add them together instead of concatenating.

But it is not good practice to store something like .09 as a number if it is actually a code of no numerical significance.

 

[gabber4858]

GlennUK,

Brilliant solution!!! I just tried it and it works great! I will have to look into the TEXT formula, never seen it before.

Sorry for no being clear, my cells were set up like below

A B C D
1 133 .09 A .01

so the formula is =A1&TEXT(B1,"#.00")&C1&TEXT(D1,"#.00")

Thanks again Glenn

 

[macropod]

Hi gabber4858,

In that case, you could also try:
=A1+B1&C1&TEXT(D1,".00")


Cheers
[MS MVP - Word]