How can I split an address into multiple cells.

[Taz1314]

I want to split one cell with several unusual types of addresses into several columns. I need to split the information into several columns. The problem I'm having is creating the correct formulas needed to extract the correct information to each cell.

1351 NE Miami Gardens Drive 203E
1872 Galleon Street apt 4
10583 NW 52nd Terrace
597 N University Drive #29

1351​	NE	Miami Gardens	Drive	203E
1872​		Galleon	Street	apt 4
10583​	NW	52nd	Terrace
597​	N	University	Drive	#29

 

[Belleke]

Something like this?

 

[Taz1314]

my computer is not letting me open it sorry

 

[ryangordon]

To split the addresses into several columns, you can use a combination of string manipulation functions in Excel. Here's an example of how you can extract different parts of the addresses into separate columns:

Assuming the addresses are in column A, you can use the following formulas in adjacent columns to extract the relevant information:

For street address:In cell B1, enter the formula:



This formula finds the first space in the address and extracts the characters before it.

For apartment/unit number:In cell C1, enter the formula:



This formula searches for the "#" character in the address and extracts the characters after it. If there is no "#" in the address, it will return an empty string.

For apartment/unit type:In cell D1, enter the formula:



This formula assumes that the apartment/unit type is always two characters before the "#". If it finds a "#", it extracts the two characters before it. If there is no "#", it will return an empty string.

For city:In cell E1, enter the formula:

=MID(A1, FIND("@", SUBSTITUTE(A1, " ", "@", LEN(A1)-LEN(SUBSTITUTE(A1, " ", ""))))+1, LEN(A1))

This formula finds the last occurrence of a space in the address and extracts the characters after it. It assumes that the city name is always after the last space.

Note: These formulas assume that the addresses follow a consistent pattern. If there are variations or additional complexities in your actual data, the formulas may need to be adjusted accordingly.

Drag these formulas down to apply them to the rest of the addresses in column A, and you should have the address components split into separate columns (street address in column B, apartment/unit number in column C, apartment/unit type in column D, and city in column E).