How do I eliminate the leading zero when concatenating?
 

Excel 2002. I created a custom number format to eliminate the leading zero.
Example: from 0.210 to .210 if there is a better way to do this please advise.
I then need to use the value from that cell in a concatenation formula.
Example: =CONCATENATE("3A",B18," ","Control Surfaces") which returns the
results: 3A0.21 Control Surfaces.
What I desire is the result: 3A.210 Control Surfaces
I need to successfully incorporate these functions as this worksheet will be
copied repeatedly with differing classifications.
Example: 3B.210 Control Surfaces; 3C.210 Control Surfaces
The concatenate formula will be used to create a list.
Example: 3A.100 Top Level
3A.110 Second Level
3A.111 Third Level
3A.200 Top Level 2 Etc.

Busy,

=CONCATENATE("3A",TEXT(B18,".000")," ","Control Surfaces")

HTH,
Bernie
MS Excel MVP


"BusyBee" wrote in message
...
Excel 2002. I created a custom number format to eliminate the leading zero.
Example: from 0.210 to .210 if there is a better way to do this please advise.
I then need to use the value from that cell in a concatenation formula.
Example: =CONCATENATE("3A",B18," ","Control Surfaces") which returns the
results: 3A0.21 Control Surfaces.
What I desire is the result: 3A.210 Control Surfaces
I need to successfully incorporate these functions as this worksheet will be
copied repeatedly with differing classifications.
Example: 3B.210 Control Surfaces; 3C.210 Control Surfaces
The concatenate formula will be used to create a list.
Example: 3A.100 Top Level
3A.110 Second Level
3A.111 Third Level
3A.200 Top Level 2 Etc.