Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract address data
PLEASE, I need a formula to extract the data of an address string.
I know that I could use the =LEFT or =MID or =RIGHT formulas, but since the lenght of the data varies, I will need something better than that. The positive thing that the database I have is consistent as show below: comma and space after the City, and space after the STate. I have this in A1: A1= Nashville, TN 37211 I would like a formula that extract the data from A1 giving me these results: B1= Nashville C1= TN D1= 37211 THANKS! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract address data
Hi,
in B1 =TRIM(LEFT(A1,FIND(",",A1)-1)) in C1 =MID(A1,SEARCH(" ",A1)+1,3) in D1 =TRIM(RIGHT(A1,FIND(" ",A1)-6)) hope this helps "Marco Margaritelli" wrote: PLEASE, I need a formula to extract the data of an address string. I know that I could use the =LEFT or =MID or =RIGHT formulas, but since the lenght of the data varies, I will need something better than that. The positive thing that the database I have is consistent as show below: comma and space after the City, and space after the STate. I have this in A1: A1= Nashville, TN 37211 I would like a formula that extract the data from A1 giving me these results: B1= Nashville C1= TN D1= 37211 THANKS! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract address data
Try these...
B1: =LEFT(A1,LEN(A1)-10) C1: =MID(A1,LEN(A1)-7,2) D1: =RIGHT(A1,5) -- Rick (MVP - Excel) "Marco Margaritelli" wrote in message ... PLEASE, I need a formula to extract the data of an address string. I know that I could use the =LEFT or =MID or =RIGHT formulas, but since the lenght of the data varies, I will need something better than that. The positive thing that the database I have is consistent as show below: comma and space after the City, and space after the STate. I have this in A1: A1= Nashville, TN 37211 I would like a formula that extract the data from A1 giving me these results: B1= Nashville C1= TN D1= 37211 THANKS! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract email(s) from address field | Excel Discussion (Misc queries) | |||
How to extract email address in hyperlink | Excel Worksheet Functions | |||
How to extract the address of a chart serie | Charts and Charting in Excel | |||
How to extract a web address from a hyperlink cell? | Excel Discussion (Misc queries) | |||
How do I extract the address from a hyperlink in excell ? | Excel Discussion (Misc queries) |