Excel - Delimiting addresses creates formulas -don't want

Question

Surely I can't be the only person who's ever have this issue.

I have a dataset which includes many hundreds of addresses in unmanagable formats, but I need to be able to sort them, so [Street Name] [Street Number] is the logical format.

My current method is to delimit then concatenate large sections at a time (some situations require more attention than others, especially where multiple street names are concerned). However, here's what happens when I delimit this range:

I tried preformatting some of the target cells to General, Text, Number, but nothing works for the whole set. Sometimes it turns it into a date, sometimes it automatically calculates division and subtraction, sometimes it does actually put it as text, but then of course I can't sort it because it's not a number.

Is there a way to avoid this from happening?

Thanks for your time.

Answer 1

No you aren't the only person facing this issue, and there are quite a few solutions available in the internet.

However they all turn out to be a bit complex and it's much simpler to understand if the same is done in vba. Nevertheless, it's still doable by excel-formula. So here's my take.

To get the result as below

you may use these formulas:

Cell B2: =IF(ISERROR(FIND(" ",A2)),A2,LEFT(A2,(FIND(" ",A2,1)-1)))
Cell C2: =MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2)-LEN(D2)-1)
Cell D2: =IF(ISERROR(FIND(" ",A2)),A2,MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))),300))

and then simply copy & paste to applicable rows below.

Note: this formula assumes the character ~ is not being used in the data set. If that isn't the case, replace that character in the formula, with the one that can't be found in the entire data set.