Md. Shamim Reza
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))
- 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)
- 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))
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″
Similar Readings
- How to Separate Address in Excel
- How to Separate City and State in Excel
- How to Separate Address Number from Street Name in Excel
- How to Separate Address in Excel Using Formula
- How to Separate City State and Zip from Address Using Excel Formula
- How to Separate City and State without Commas 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.
- 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.
- 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.
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.
