Data cleanup
 

I am importing data from a website into a SS and I have a field I need to
cleanup so I can run a vlookup against it. The data comes over three ways
and looks like this:

ex1: word1 word2 , other words; I need 'word1 word2'
ex2: word1 word2 1, other words; I need 'word1 word2'
ex3: word1, other words; I need 'word1'

I'm interested in the data left of the comma and I need to strip any
trailing spaces and the ' 1' (space 1). I'm getting close by: Data-Text to
Columns and using the comma delimiter THEN find/replace 'space 1' w/nothing.
But I am left with some cells that have a trailing space (example 1 above).
I ran the Trim function but it's not removing the trailing space.

Any help would be greatly appreciated.

tks,
steve

Data cleanup
 

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,",",REPT(" ",99))," 1",""),99))


"sjs" wrote:

I am importing data from a website into a SS and I have a field I need to
cleanup so I can run a vlookup against it. The data comes over three ways
and looks like this:

ex1: word1 word2 , other words; I need 'word1 word2'
ex2: word1 word2 1, other words; I need 'word1 word2'
ex3: word1, other words; I need 'word1'

I'm interested in the data left of the comma and I need to strip any
trailing spaces and the ' 1' (space 1). I'm getting close by: Data-Text to
Columns and using the comma delimiter THEN find/replace 'space 1' w/nothing.
But I am left with some cells that have a trailing space (example 1 above).
I ran the Trim function but it's not removing the trailing space.

Any help would be greatly appreciated.

tks,
steve