I have the following field:

=LEFT((T3)&"000",3)

It returns B00 or AL0, etc. depending on the value of T3. However, I
need it to take the value of T3 (ie: A, BL, R, etc) and make it take 3
spaces without the zeros?

Does that make sense? Something like,

AL(space) or B(space)(space) or if empty (space)(space)(space). This
is then put into a join string.

I can't seem to find a way to do this. Any ideas?

Thanks!


--
denileigh
------------------------------------------------------------------------
denileigh's Profile: http://www.excelforum.com/member.php...o&userid=30211
View this thread: http://www.excelforum.com/showthread...hreadid=531666

=LEFT(T3&" ",3)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"denileigh" wrote
in message ...

I have the following field:

=LEFT((T3)&"000",3)

It returns B00 or AL0, etc. depending on the value of T3. However, I
need it to take the value of T3 (ie: A, BL, R, etc) and make it take 3
spaces without the zeros?

Does that make sense? Something like,

AL(space) or B(space)(space) or if empty (space)(space)(space). This
is then put into a join string.

I can't seem to find a way to do this. Any ideas?

Thanks!


--
denileigh
------------------------------------------------------------------------
denileigh's Profile:
http://www.excelforum.com/member.php...o&userid=30211
View this thread: http://www.excelforum.com/showthread...hreadid=531666

Try:

=SUBSTITUTE(LEFT((T3)&"000",3),0," ")

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"denileigh" wrote
in message ...

I have the following field:

=LEFT((T3)&"000",3)

It returns B00 or AL0, etc. depending on the value of T3. However, I
need it to take the value of T3 (ie: A, BL, R, etc) and make it take 3
spaces without the zeros?

Does that make sense? Something like,

AL(space) or B(space)(space) or if empty (space)(space)(space). This
is then put into a join string.

I can't seem to find a way to do this. Any ideas?

Thanks!


--
denileigh
------------------------------------------------------------------------
denileigh's Profile:
http://www.excelforum.com/member.php...o&userid=30211
View this thread: http://www.excelforum.com/showthread...hreadid=531666