Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/replace
have a listing of 500 mailing type labels downloaded from web. i i want to
parse into columns... problem with the address portion e.g., 123 W Main St Columbus, Oh 46555 18759 W 1700th Rd New Providence, OH 46555 There seem to be 3 spaces between the ending of the street addr and the start of the city as the only consistency in the scheme. i want to try to use find/replace and put delimiter in, then use the text to column. F/replace doesn't seem to recognize more than one space?? Any ideas? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/replace
With Text to Columns has a 'Treat consecutive delimiters as one' checkbox,
so 3 spaces shouldn't matter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "reno" wrote in message ... have a listing of 500 mailing type labels downloaded from web. i i want to parse into columns... problem with the address portion e.g., 123 W Main St Columbus, Oh 46555 18759 W 1700th Rd New Providence, OH 46555 There seem to be 3 spaces between the ending of the street addr and the start of the city as the only consistency in the scheme. i want to try to use find/replace and put delimiter in, then use the text to column. F/replace doesn't seem to recognize more than one space?? Any ideas? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/replace
Bob the textcolumn scheme doesn't seem to work correctly...here are a few of
the actual address: 1911 - 290th Street Greenfield 6212 N 131st St Omaha 2773 290th St Orient 2956 North View Road Dorchester 187 Dorchester Drive Dorchester 2770 Schulte Rd Dorchester 1509 Dry Ridge Dr Lansing 2601 LaFayette Ridge Drive Lansing i've been able to parse the state and zip code out. i tried the consecutive and did not get the correct results, unless i then go back and & concatenate the cells to reconstuct the number and street addr. i thought the findreplace might do it, no joy so far. thanks "Bob Phillips" wrote: With Text to Columns has a 'Treat consecutive delimiters as one' checkbox, so 3 spaces shouldn't matter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "reno" wrote in message ... have a listing of 500 mailing type labels downloaded from web. i i want to parse into columns... problem with the address portion e.g., 123 W Main St Columbus, Oh 46555 18759 W 1700th Rd New Providence, OH 46555 There seem to be 3 spaces between the ending of the street addr and the start of the city as the only consistency in the scheme. i want to try to use find/replace and put delimiter in, then use the text to column. F/replace doesn't seem to recognize more than one space?? Any ideas? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/replace
I see the problem, it is not 3 spaces but it is spaces in the street.
Try this in one column, get the street with =LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) in another, get the city with =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "reno" wrote in message ... Bob the textcolumn scheme doesn't seem to work correctly...here are a few of the actual address: 1911 - 290th Street Greenfield 6212 N 131st St Omaha 2773 290th St Orient 2956 North View Road Dorchester 187 Dorchester Drive Dorchester 2770 Schulte Rd Dorchester 1509 Dry Ridge Dr Lansing 2601 LaFayette Ridge Drive Lansing i've been able to parse the state and zip code out. i tried the consecutive and did not get the correct results, unless i then go back and & concatenate the cells to reconstuct the number and street addr. i thought the findreplace might do it, no joy so far. thanks "Bob Phillips" wrote: With Text to Columns has a 'Treat consecutive delimiters as one' checkbox, so 3 spaces shouldn't matter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "reno" wrote in message ... have a listing of 500 mailing type labels downloaded from web. i i want to parse into columns... problem with the address portion e.g., 123 W Main St Columbus, Oh 46555 18759 W 1700th Rd New Providence, OH 46555 There seem to be 3 spaces between the ending of the street addr and the start of the city as the only consistency in the scheme. i want to try to use find/replace and put delimiter in, then use the text to column. F/replace doesn't seem to recognize more than one space?? Any ideas? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/replace
i inserted the formulas and got a #Value error, now i'm really confused. not
sure why the error message. i've 3x-ck the formula and it seems to be correct. i appreciate your help on this! "reno" wrote: have a listing of 500 mailing type labels downloaded from web. i i want to parse into columns... problem with the address portion e.g., 123 W Main St Columbus, Oh 46555 18759 W 1700th Rd New Providence, OH 46555 There seem to be 3 spaces between the ending of the street addr and the start of the city as the only consistency in the scheme. i want to try to use find/replace and put delimiter in, then use the text to column. F/replace doesn't seem to recognize more than one space?? Any ideas? Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find/replace
maybe word-wrap, try
in one column, get the street with =LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)- LEN(SUBSTITUTE(A1," ",""))))-1) in another, get the city with =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)- LEN(SUBSTITUTE(A1," ",""))))+1,255) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "reno" wrote in message ... i inserted the formulas and got a #Value error, now i'm really confused. not sure why the error message. i've 3x-ck the formula and it seems to be correct. i appreciate your help on this! "reno" wrote: have a listing of 500 mailing type labels downloaded from web. i i want to parse into columns... problem with the address portion e.g., 123 W Main St Columbus, Oh 46555 18759 W 1700th Rd New Providence, OH 46555 There seem to be 3 spaces between the ending of the street addr and the start of the city as the only consistency in the scheme. i want to try to use find/replace and put delimiter in, then use the text to column. F/replace doesn't seem to recognize more than one space?? Any ideas? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find/replace enters | Excel Discussion (Misc queries) | |||
Find/replace search field defaults | Excel Discussion (Misc queries) | |||
How to find/replace data in column | Excel Discussion (Misc queries) | |||
How to find/replace data in column | New Users to Excel | |||
Find/Replace carriage return & line feed characters in Excel. | Excel Worksheet Functions |