How do I eliminate the leading zero when concatenating?

[BusyBee]

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.

[Bernie Deitrick]

Busy,

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

HTH,
Bernie
MS Excel MVP


"BusyBee" <BusyBee@discussions.microsoft.com> wrote in message
news:BEF81D88-9A13-4175-B246-430CB1A7971D@microsoft.com...
> 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.
>
>