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

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.


Download Practice Workbook

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


2 Ways to Split Inconsistent Address in Excel

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.

=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 Number from Street Name in Excel (6 Ways)


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 (3 Easy Methods)


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.

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. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo