Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting data for address lists
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)? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting data for address lists
Hi
Goto Data Text To Columns Choose Delimited and let the wizard guide you through. Regards, Per "firestorm1954" skrev i meddelelsen ... 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)? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting data for address lists
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)? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting data for address lists
Highlight the column and click on Data | Text to Columns, choosing
delimited and comma as delimiter. Hope this helps. Pete On Apr 30, 8:49*am, 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)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting embbeded web address links | Excel Discussion (Misc queries) | |||
Excel Address Lists | Excel Discussion (Misc queries) | |||
Extracting out of a emai address string | Excel Discussion (Misc queries) | |||
Comparing two lists and extracting data from one to another | Excel Worksheet Functions | |||
Extracting # numbers from address | Excel Discussion (Misc queries) |