Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find max then sum cells above & to the left? | Excel Worksheet Functions | |||
Use of Find with Left, Mid, Right functions in nested IF(and('s | Excel Discussion (Misc queries) | |||
TO FIND VALUE TO THE LEFT THE CELL USING VLOOKUP OR ANY OTHER | Excel Discussion (Misc queries) | |||
Find LARGE, and th cell 3 cells to the left? | Excel Worksheet Functions | |||
FIND or LEFT or MID to swap first name with last name? | Excel Worksheet Functions |