Extracting data for address lists

[firestorm1954]

I can use LEFT and MID to split certain lists, but where I have a whole
address in a cell, separated by commas, I need to take the data before the
first comma to another cell, the data between the next pair of commas to
another cell and so on. I can do this labouriously column by column, but is
there any shorter way where the program can look for data between sets of
characters (in this case the commas)?

 

[Per Jessen]

Hi

Goto Data > Text To Columns > Choose Delimited and let the wizard guide you
through.

Regards,
Per

 

[Mike H]

Hi,

Posting a sample of your data would have been helpful because there are a
couple of ways of doing this. The simplest is probably using

Data|Text to columns and splitting the data in that way.

Another method involves finding the commas using FIND and then using Left,
right or Mid to seperate the data like this:
=FIND(",",A1,1)
finds the first comma so
=LEFT(A1,FIND(",",A1,1)-1)
Returns everyting before that comma

To find the second comma use
=FIND(",",A1,FIND(",",A1,1)+1)
This the same as the first find but because we know the position of the
first comma from that formula we can use that position (+1) to find the
second so now using mid the text between the first and second comma can be
extracted.

=MID(A1,FIND(",",A1,1)+1,FIND(",",A1,FIND(",",A1,1)+1)-FIND(",",A1,1)-1)

Mike

 

[Pete_UK]

Highlight the column and click on Data | Text to Columns, choosing
delimited and comma as delimiter.

Hope this helps.

Pete