Extract comma broken address to cells
I have address's that have been imported from a delimited text file in the
following format. 9,Hall,Street,Billytown,6666 I would like to extract it into colums ie Column 1 9 Column 2 Hall Street Column 3 Billytown 6666 |
Take a look at DataText To columns, and use a comma delimiter
-- HTH RP (remove nothere from the email address if mailing direct) "bbc1" wrote in message ... I have address's that have been imported from a delimited text file in the following format. 9,Hall,Street,Billytown,6666 I would like to extract it into colums ie Column 1 9 Column 2 Hall Street Column 3 Billytown 6666 |
This puts all into seperate columns need as explained
"Bob Phillips" wrote: Take a look at DataText To columns, and use a comma delimiter -- HTH RP (remove nothere from the email address if mailing direct) "bbc1" wrote in message ... I have address's that have been imported from a delimited text file in the following format. 9,Hall,Street,Billytown,6666 I would like to extract it into colums ie Column 1 Number Column 2 Hall Street Column 3 Billytown 6666 |
Sorry, I missed that nuance.
Think you have a problem, as a formula will need some definite data structure,. What happens to say 9,St,James,Street,Billytown how do we know the street has 1, 2, 3 or however many components? -- HTH RP (remove nothere from the email address if mailing direct) "bbc1" wrote in message ... This puts all into seperate columns need as explained "Bob Phillips" wrote: Take a look at DataText To columns, and use a comma delimiter -- HTH RP (remove nothere from the email address if mailing direct) "bbc1" wrote in message ... I have address's that have been imported from a delimited text file in the following format. 9,Hall,Street,Billytown,6666 I would like to extract it into colums ie Column 1 Number Column 2 Hall Street Column 3 Billytown 6666 |
All address have the same make up. As shown.
"Bob Phillips" wrote: Sorry, I missed that nuance. Think you have a problem, as a formula will need some definite data structure,. What happens to say 9,St,James,Street,Billytown how do we know the street has 1, 2, 3 or however many components? -- HTH RP (remove nothere from the email address if mailing direct) "bbc1" wrote in message ... This puts all into seperate columns need as explained "Bob Phillips" wrote: Take a look at DataText To columns, and use a comma delimiter -- HTH RP (remove nothere from the email address if mailing direct) "bbc1" wrote in message ... I have address's that have been imported from a delimited text file in the following format. 9,Hall,Street,Billytown,6666 I would like to extract it into colums ie Column 1 Number Column 2 Hall Street Column 3 Billytown 6666 |
OK, add this to B1 and copy down
=LEFT(A1,FIND(",",A1,FIND(",",A1)+1)-1)&" "&MID(A1,FIND(",",A1,FIND(",",A1)+1)+1,99) then do Text to Columns on B -- HTH RP (remove nothere from the email address if mailing direct) "bbc1" wrote in message ... All address have the same make up. As shown. "Bob Phillips" wrote: Sorry, I missed that nuance. Think you have a problem, as a formula will need some definite data structure,. What happens to say 9,St,James,Street,Billytown how do we know the street has 1, 2, 3 or however many components? -- HTH RP (remove nothere from the email address if mailing direct) "bbc1" wrote in message ... This puts all into seperate columns need as explained "Bob Phillips" wrote: Take a look at DataText To columns, and use a comma delimiter -- HTH RP (remove nothere from the email address if mailing direct) "bbc1" wrote in message ... I have address's that have been imported from a delimited text file in the following format. 9,Hall,Street,Billytown,6666 I would like to extract it into colums ie Column 1 Number Column 2 Hall Street Column 3 Billytown 6666 |
All times are GMT +1. The time now is 12:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com