Help putting left, right, find and len together

[Glenda]

I need to pull the following string apart in several different ways and I
can't get the combination of the functions right for it to work.

example of string:
AB 5X75X200 YD G5487L

First - I want to look at the string and pull out the dimensions 5, 75, and
200 and put them in separate cells. character counts will not always be the
same

Second - YD may not always be YD it could also be LY

Third - I need to start at the right find the space and pull everything out
to the right of the space.

 

[Sean Timmons]

=MID(A2,FIND(" ",A2)+1,FIND("X",A2)-FIND(" ",A2)-1)

=MID(A2,FIND("X",A2)+1,FIND("X",A2,FIND("X",A2)+1)-FIND("X",A2)-1)

=MID(A2,FIND("X",A2,FIND("X",A2)+1)+1,FIND("
",A2,FIND("X",A2,FIND("X",A2)+1)-FIND("X",A2)-1))

=MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,FIND(" ",A2,FIND(" ",A2,FIND("
",A2)+1)+1-FIND(" ",A2,FIND(" ",A2)+1))-1)

=RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,FIND(" ",A2)+1)+1))

Yowza!

 

[Mike H]

Hi,
Try these

for 5
=MID(A1,SEARCH(" ",A1)+1,SEARCH("x",A1)-(SEARCH(" ",A1)+1))

For 75
=MID(A1,SEARCH("x",A1)+1,SEARCH("x",A1,SEARCH("x",A1)+1)-(SEARCH("x",A1)+1))

For 200
=MID(A1,SEARCH("x",A1,SEARCH("x",A1)+1)+1,SEARCH(" ",A1,SEARCH("
",A1)+1)-(SEARCH("x",A1,SEARCH("x",A1)+1)+1))

For the right bit
=MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1),LEN(A1))

Mike

 

[MartinW]

Hi Glenda,

I put your example in A1 and went to Data>Text to Columns
Checked Delimited and Next
Checked Space and checked Other and put an X in the input box
then clicked Finish.

I ended up with
AB | 5 | 75 | 200 | YD | G5487L
in A1 to F1.

Is that what you are looking for?

HTH
Martin

 

[Sean Timmons]

Now that is taking all the fun out! That's a lot easier....

 

[MartinW]

Easier? Yes.
But is it what Glenda is trying to achieve?
That, we don't know yet.

Regards
Martin