Extract comma broken address to cells

B

bbc1

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
 
B

Bob Phillips

Take a look at Data>Text To columns, and use a comma delimiter

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

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)
 
B

bbc1

All address have the same make up. As shown.

Bob Phillips said:
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)
Click to expand...
 
B

Bob Phillips

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)
 
Top