How to Split Inconsistent Address in Excel (2 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to split inconsistent address in Excel. There are several ways to split consistent addresses in MS Excel. For example, the LEFT, MID, and RIGHT functions, the Text To Column feature, etc. But, it becomes harder to do that when the addresses do not follow a particular pattern. We will show you 2 possible solutions to that scenario.


How to Split Inconsistent Address in Excel: 2 Effective Ways

Assume you have the following dataset. It contains a list of addresses with the city, state, and zip code. They do not strictly follow any particular patterns. Follow the methods below to split these inconsistent addresses in Excel.


1. Split Inconsistent Address in Excel with Custom Formula

Follow the steps below to split the inconsistent addresses using custom Excel formulas.

📌 Steps

  • First, we need a user-defined function (UDF) to create the custom formula. Therefore, press ALT+F11 to open the VBA window. Then, select Insert >> Module to create a new module. Alternatively, you can use the ALT+I+M keyboard shortcut to do that.

  • Next, copy the following code using the copy button.
Function REV(s As String)
REV = StrReverse(s)
End Function
  • After that, paste the copied code on the blank module as shown in the picture below. Then, save the document as a macro-enabled workbook.

  • Now, enter the following formula in cell C5 to split the city name from the address. Then, apply the formula to the cells below using the fill handle icon.
=LEFT(B5,LEN(B5)-(FIND(" ",REV(B5),1)+3))

Split Inconsistent Address in Excel with Custom Formula

  • Then, enter the following formula in cell D5 to split the state name from the addresses. Then, drag the fill handle icon to copy the formula down to the cells below.
=MID(B5,LEN(B5)-(FIND(" ",REV(B5),1)+1),2)

Split Inconsistent Address in Excel with Custom Formula

  • After that, enter the following formula in cell E5 to split the zip code from the address. Then, double-click on the Fill Handle icon to copy the formula down to the cells below.
=RIGHT(B5,FIND(" ",REV(B5),1))

Split Inconsistent Address in Excel with Custom Formula

Formula Breakdown

✅ LEFT(B5,LEN(B5)-(FIND(” “,REV(B5),1)+3))

➤ REV(B5)
This is the user-defined function (UDF). It reverses the order of the letters and digits of any string.
Output: “50221 YN YNABLA”

➤ FIND(” “,REV(B5),1)
The FIND function is a case-sensitive function that returns the starting position of one text string within another text string.
Output: 6

➤ LEN(B5)
The LEN function returns the number of characters of a text string.
Output: 15

➤ LEN(B5)-(FIND(” “,REV(B5),1)+3))
Output: 6

➤ LEFT(B5,LEN(B5)-(FIND(” “,REV(B5),1)+3))
The LEFT function returns the specified number of characters from the start of a text string.
Output: “ALBANY”

✅ MID(B5,LEN(B5)-(FIND(” “,REV(B5),1)+1),2)
The MID function returns the characters from the middle of a text string. Given a starting position and length.
Output: “NY”

✅ RIGHT(B5,FIND(” “,REV(B5),1))
The RIGHT function returns the specified number of characters from the end of a text string.
Output: ” 12205″

Read More: How to Separate Address in Excel


2. Split Inconsistent Address with Power Query in Excel

Alternatively, you can also use the Power Query in excel to get the same result. Follow the steps below to do that.

📌 Steps

  • First, click anywhere in your dataset. Then, select Data >> From Table/Range as shown in the following picture. Excel will automatically detect the range. Otherwise, use the upward arrow to select the proper range. Then, click OK.

  • After that, the Power Query Editor will get opened. Now, select Split Column >> By Delimiter from the Home tab in the editor.

  • Then, choose “Space” as the delimiter and split at the Right-most delimiter. Next, click OK.

  • After that, the zip codes will split into a new column as shown below. Now, select Split Column >> By Delimiter as earlier.

Split Inconsistent Address with Power Query in Excel

  • Then, choose “Space” as the delimiter and split at the Right-most delimiter. Next, click OK.

  • After that, state names will split into a new column as shown below. But, the state name in the third address is separated by a comma. Therefore, the respective cell in the split column shows “null”.
  • Now, select Split Column >> By Delimiter again to fix this issue.

Split Inconsistent Address with Power Query in Excel

  • But, choose “Comma” as the delimiter this time.

  • Then, the state name from that cell will split into a new column. Now select this column (if it isn’t already) by clicking on the column header. Then, hold CTRL and select the other column containing the state names.
  • After that, right-click on the header of any of the selected columns. Next, select Merge Columns as shown in the picture below.

  • You can enter a name for the merged column. Then click OK.

  • After that, the state names will get merged into a single column as shown below. Now, double-click on the headers to change the names of the columns.

  • After that, select Close & Load To >> Close & Load To…

  • Then, mark the radio button for Existing Worksheet. Next, use the upward arrow to enter the location where you want to get the split addresses. Click OK after that.

  • Finally, you will get the following result.

Split Inconsistent Address with Power Query in Excel

Read More: How to Separate Address in Excel with Comma


Things to Remember

  • You must same the document as a macro-enabled workbook to avoid losing the code.
  • You should choose the delimiter in the Power Query based on your dataset.

Download Practice Workbook

You can download the practice workbook from the download button below.


Conclusion

Now you know how to split inconsistent address in excel in 2 different ways. You may try to apply it to the practice example. If you have further queries or suggestions, please let us know in the comment section below. Stay with us and keep learning.


Related Articles


<< Go Back to Address Format | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo