RE: Extracting data for address lists
- From: Mike H <MikeH@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 30 Apr 2008 01:06:01 -0700
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
"firestorm1954" wrote:
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)?
- References:
- Extracting data for address lists
- From: firestorm1954
- Extracting data for address lists
- Prev by Date: Re: Extracting data for address lists
- Next by Date: Re: Extracting data for address lists
- Previous by thread: Re: Extracting data for address lists
- Next by thread: Re: Extracting data for address lists
- Index(es):
Relevant Pages
|
