Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cleanup worksheets from 97-2003 to work properly on Excel 2007 | Excel Worksheet Functions | |||
Pivot Table Cleanup | Excel Discussion (Misc queries) | |||
Can someone help Cleanup my recorded macro | Excel Discussion (Misc queries) | |||
recently used files, cleanup | Excel Discussion (Misc queries) | |||
Windows XP Disc Cleanup | New Users to Excel |