Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/Replace or VBA
I have downloaded 600 name address list from web. i'm tryin to parse the
address. an address example is show he 2449 63rd St Vinton 503 W 6th St Vinton 5378 - 29th Avenue Vinton 525 C Ave. W Norway 2414 76 St Watkins There seem to be 5 blank spaces between the last of the physical addr and the start of the city. Find/Replace does not seem to work, the text/col doesn't recognize the 5 spaces. Any ideas? Thx |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/Replace or VBA
Since you got them from the Web, maybe they're not spaces--they could be those
troublesome HTML non-breaking spaces (x160). Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm If those 5 "spaces" are the only char(160)'s in the cell, you may be able to just: Select the range data|text to columns delimited by other hit and hold the alt key while typing 0160 using the numeric keypad and check that box to "treat consecutive delimiters as one" And finish up that dialog. reno wrote: I have downloaded 600 name address list from web. i'm tryin to parse the address. an address example is show he 2449 63rd St Vinton 503 W 6th St Vinton 5378 - 29th Avenue Vinton 525 C Ave. W Norway 2414 76 St Watkins There seem to be 5 blank spaces between the last of the physical addr and the start of the city. Find/Replace does not seem to work, the text/col doesn't recognize the 5 spaces. Any ideas? Thx -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/Replace or VBA
you're right on the chr.
it shows Dec 160 160 032 160 between the street addr and start of city. suggestions? thx "reno" wrote: I have downloaded 600 name address list from web. i'm tryin to parse the address. an address example is show he 2449 63rd St Vinton 503 W 6th St Vinton 5378 - 29th Avenue Vinton 525 C Ave. W Norway 2414 76 St Watkins There seem to be 5 blank spaces between the last of the physical addr and the start of the city. Find/Replace does not seem to work, the text/col doesn't recognize the 5 spaces. Any ideas? Thx |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/Replace or VBA
Select the range
Edit|replace what: spacebar, then alt-0160 with: (leave blank) replace all Then use data|text to columns using alt-0160 as the delimiter. reno wrote: you're right on the chr. it shows Dec 160 160 032 160 between the street addr and start of city. suggestions? thx "reno" wrote: I have downloaded 600 name address list from web. i'm tryin to parse the address. an address example is show he 2449 63rd St Vinton 503 W 6th St Vinton 5378 - 29th Avenue Vinton 525 C Ave. W Norway 2414 76 St Watkins There seem to be 5 blank spaces between the last of the physical addr and the start of the city. Find/Replace does not seem to work, the text/col doesn't recognize the 5 spaces. Any ideas? Thx -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/Replace or VBA
Dave you're the Man, that worked! Thanks!! "reno" wrote: you're right on the chr. it shows Dec 160 160 032 160 between the street addr and start of city. suggestions? thx "reno" wrote: I have downloaded 600 name address list from web. i'm tryin to parse the address. an address example is show he 2449 63rd St Vinton 503 W 6th St Vinton 5378 - 29th Avenue Vinton 525 C Ave. W Norway 2414 76 St Watkins There seem to be 5 blank spaces between the last of the physical addr and the start of the city. Find/Replace does not seem to work, the text/col doesn't recognize the 5 spaces. Any ideas? Thx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find/replace | Excel Discussion (Misc queries) | |||
find/replace enters | Excel Discussion (Misc queries) | |||
Find/replace search field defaults | Excel Discussion (Misc queries) | |||
Word Automation - Find/Replace | Excel Discussion (Misc queries) | |||
Find/Replace carriage return & line feed characters in Excel. | Excel Worksheet Functions |