extracting text between variable set variables

[kevinlasvegas]

DESCRIPTION

9600DS 3/16OD CXC DIMP-STOP COUPLING

9600RS 3/16OD CXC ROLL-STOP COUP W01001

9600DS 1/4 CXC DIMP-STOP COUPLING W10141

9600RS 1/4 CXC ROLL-STOP COUPLING W01003

9600R 1/4X3/16OD CXC RED COUPLING W01004

9600DS 5/16OD CXC DIMP-STOP COUP W10142

9600RS 5/16OD CXC ROLL-STOP COUP W01006

9600R 5/16ODX1/4 CXC RED COUPLING

<tbody>
</tbody>

Hello Mr Excel,
Long time lurker, first time poster.
My data set has a variable length ALPHANUMERIC LEADER and in some cases a variable length ALPHANUMERIC TRAILER that always,(when it is there) starts in a "A" or "W" followed by a set of numbers.
The LEADER is always separated by a space AFTER it and the leader is in every cell.
The TRAILER (when present) is always separated by a space BEFORE the "A" or "W" but may not exist in every cell.
This means I can not extract with a MID function which will extract between the first and last space. If no TRAILER exists I need the LEADER stripped and the results returned without an error resulting.
I guess it is a combination of IF, FIND,LEFT, RIGHT all in one nested formula. I have tried every variation of a MID function nested with CRITERIA functions and I have not hit the jackpot yet. Now I have run out of ideas. I need serious help.
Thank you.

 

[István Hirsch]

If the number of spaces was "constant (ref. post #20) then this would be enough:

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),100,400))

I do not think so, for example "ROLL-STOP" is often seen as "ROLL STOP"

 

[Rick Rothstein]

If the number of spaces was "constant (ref. post #20) then this would be enough:

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),100,400))

I do not think so, for example "ROLL-STOP" is often seen as "ROLL STOP"

It is my impression that the OP posted a small sample from a much larger set of product descriptions. Given that, I would be surprised if every description for every product consisted of exactly the same number of "words" nor, referencing others formulas, whether they all started with a fraction so that there would always be a slash to lock onto.

 

[kevinlasvegas]

RR is correct. The number of spaces is a variable beyond capture. The variations in spelling is also not able to captured.
My goal is to clean up a very high percentage of the data through an excel based solution and then only have to manually clean up a dozen or so lines per data set.
In total this project will have between 50K & 60K lines from about 50 data sets. The data sets are all variations on a theme with the description field being the single most important field to clean up.
All other fields easily managed.
This is going to include every plumbing item you can think of and all the ones you have never heard of.
We have selected a group of median priced manufacturers that cover 98% of what we will ever need and I need to clean up the data sets and organize the lists into an easily read and easy to choose manner where the costs all work themselves out and the project is bid by project phase all without needing call in and then wait for price quotes from suppliers. I have a matrix of supplier price multipliers and a formula that accurately selects the correct one. I have created a price markup matrix and a similar formula to select the correct markup. I have a method of adding new & custom items, new suppliers and new multipliers & markups.
All the estimator should need to do is enter a quantity and move on.
Everything should be easy to read for the estimators. When they are working on a bid they don't need extra characters confusing things.
This really is the final thing to do for this and with tweaking, ( now that I have read it to make sure I understand it ) I can use it to cover most of what I need to clean up.
Thank you & Merry Christmas to one and all. Sorry to babble ...the Patriots won and the eggnog has taken hold for this evening.
Kev in Las Vegas

 

[kevinlasvegas]

LockGarmin...Thanks for the education. I will take advantage of it.
K