Help putting left, right, find and len together
 

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.

Help putting left, right, find and len together
 

=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!
"Glenda" wrote:

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.

Help putting left, right, find and len together
 

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


"Glenda" wrote:

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.

Help putting left, right, find and len together
 

Hi Glenda,

I put your example in A1 and went to DataText 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



"Glenda" wrote in message
...
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.

Help putting left, right, find and len together
 

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

"MartinW" wrote:

Hi Glenda,

I put your example in A1 and went to DataText 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



"Glenda" wrote in message
...
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.

Help putting left, right, find and len together
 

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

Regards
Martin

"Sean Timmons" wrote in message
...
Now that is taking all the fun out! That's a lot easier....

"MartinW" wrote:

Hi Glenda,

I put your example in A1 and went to DataText 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



"Glenda" wrote in message
...
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.