Extract and/or Split the Address Direction and Street Name

[Iamatwork]

Hello Everyone,

I am trying to find a way/formula to extract address direction into one row and the street name in separate row. The address come in different format and the Direction is not always there. (Directions are North, West, South, East, NE. etc..)

For example A1: 1230 North Bam Bam Street
Result would be: A2 = North ; A3 = Bam Bam Street

Here are what I tried so far

1. =RIGHT(A1,LEN(A1)-SEARCH({"North","South"}, A1)-1) ..... if I use this formula, then it will flash spill into multiple rows based on {"North","South"}
2. =TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),COLUMNS($A1:B1)*LEN($A1)-(LEN($A1)-1),LEN($A1))) ....... if I use this one to split as delimiter then the return value not always in the same nth column
3. =IFERROR(INDEX(Directions!A1:A18,MATCH(TRUE,ISNUMBER(SEARCH(Directions!A1:A18,A2)),0)),"").......if I use this one, sometime it return the directions pulling from the hard coded list even when the address doesn't contain the direction
4. =IFERROR(RIGHT(A1,LEN(A1) - SEARCH(" ", A1, SEARCH(" ", 1) + 2)),"")......if I use this then the Street name return as "north Bam Bam Street"

I'm not an expert by any mean, any feedback is greatly appreciated

Various Address Format
123 W. 10th Street
1234 Two Ways Blvd, Apt #2
9890 West Paseo De Charros Dr.

[Glenn Kennedy]

One way:

=--LEFT(A1,SEARCH(" ",A1)-1)

=IFERROR(LOOKUP(1000,SEARCH({"W.","West ","N.","North "},A1),{"W.","West ","N.","North "}),"")

adapt by adding in other terms. NOTE - space after "West" to become "WEST ". add in the others yourself, as needed.

=TRIM(SUBSTITUTE(A1,TRIM(B1&" "&C1)&" ",""))

[Iamatwork]

Thanks, Glenn. I'll try this out and report back

[Iamatwork]

This works great, thank you again!

[FlameRetired]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[cooltaz]

Hi Glen,

I modify your equation to include all the directional prefix. Sometimes I get address with directional prefix in forms of "W" or "W." or West" Some addresses are fine but some yield erratic results, such as cell C2 C3 C16 C17 and D2 D3 D16 D17. I don't know coding and not much of an excel user. Your help is greatly appreciated.

[Glenn Kennedy]

COOLTAZ.

Hi.

It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!


The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.

Thread closed.