Formatting Question

Posted by Jerry on October 16, 2001 5:08 AM

I have a formatting question for you:

Given:
HW 16-A4
JB H4
CB K1A

I want output:
16-A4
H4
K1A

How do I strip backwards to the space? Something like
=LEFT(CELL,"tospace")

Thanks for your help!

Posted by Dan on October 16, 2001 5:12 AM

Is the space always the 3rd character?
If so you could use (assuming A1 as the target cell):

=RIGHT(A1,LEN(A1)-3)

Posted by IML on October 16, 2001 6:04 AM

You could use
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Posted by Jerry on October 16, 2001 6:17 AM

Thanks to both of you!

Posted by Mark W. on October 16, 2001 6:20 AM

Use the Data | Text to Columns... menu command.
Select "Delimited" at Step 1 of 3. Check "Space"
at Step 2 of 3. For the 1st column (i.e., "HW")
select "Do not import..." at Step 3 of 3 and
press [ Finish ].