Address Formulas: How to separate ### from an address.

[JERRY_MD]

How do you separate ### from address. I am trying to parse into rows a full address. Separating the number into a separate column, Street name into a separate column.

Any assistance would be appreciative.

 

[Joe4]

The problem is that address come in many different formats. So trying to determine a formula to handle all scenarios may be very tricky (ot maybe not even possible).

However, to handle the most common occurrence, where you have a number follow by the street name, we can devise some formulas to look for the first space and separate everything to the left and right of it.

So, if our address were in cell A1, our two formulas would be:
=LEFT(A1,FIND(" ",A1,1)-1)
and
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))