Address has house number at end, sometimes - need split

[ctackett6407]

Hey folks had a question that I wanted a better solution then what I came up with, partially came up with.

The address Column starts in B2 (examples below)

I tried this in C column
=LEFT(B21332,LEN(B21332)-LEN(D21332)-1)

I tried this in D column
=TRIM(RIGHT(SUBSTITUTE(B21332," ",REPT(" ",LEN(B21332))),LEN(B21332)))

As you can see below my D column is giving me all kinds of results and I'm not sure how to grab the right data, I know I won't be able to fix them all but I'm wanting to not split the addresses where the Numbers are at the start of the line because those are the house numbers.

In the other columns I'm trying to grab the house number which these folks put at the end of the street address except .. not all the street addresses are ending in house numbers but contain LOT's etc..

I'm trying to figure out how to ignore the value if the house number is at the start.. and split the house number to it's own column and if anything comes after the house number it can go into it's own Unit Column,

I'm not sure how to accomplish this so seeking help from the more experienced.

1234 US HWY 10 SOUTH A KIRKLY ST BEHIND 252 MAXWELL RD EXT (SHOP) 845 MAXWELL RD EXT 843 BARN SEASHIP LN 333 TLR #11 ALFRED CIR 235 APT D-7 BRAVO LN 700 LOT 421,432 SOBERWICK AVE 3632 KITTEN CROW RD REMOTE US HWY 252 N (BLK BLDG) 3462

 

[JEC]

Don't know what you exactly want. Give some desired results for better solutions.
But you could try this UDF

Function jec(cell As String) As Variant
 a = Split(Replace(cell, ",", ", "))(UBound(Split(Replace(cell, ",", ", "))))
 If IsNumeric(a) Then
    jec = a
 Else
    For Each it In Split(Split(cell, " ", 2)(1))
      If IsNumeric(it) Then jec = it: Exit Function
    Next
 End If
End Function

 

[ctackett6407]

@JEC Sorry, I couldn't figure out how to edit the original post for some reason.

The example is what I have and the goal is to split it

	Original Address	Address 1	Number	Unit
	1234 US HWY 10 SOUTH	1234 US HWY 10 SOUTH
	A KIRKLY ST BEHIND 252	A KIRKLY ST BEHIND	252​
	MAXWELL RD EXT (SHOP) 845	MAXWELL RD EXT (SHOP)	845​
	MAXWELL RD EXT 843 BARN	MAXWELL RD EXT	843​	BARN
	SEASHIP LN 333 TLR #11	SEASHIP LN	333​	TLR #11
	ALFRED CIR 235 APT D-7	ALFRED CIR	235​	APT D-7
	BRAVO LN 700 LOT 421,432	BRAVO LN	700​	LOT 421,432
	SOBERWICK AVE 3632	SOBERWICK AVE	3632​
	KITTEN CROW RD REMOTE	KITTEN CROW RD REMOTE
	US HWY 252 N (BLK BLDG) 3462	US HWY 252 N (BLK BLDG)	3462​

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[JEC]

This UDF spills it the right way at once

Function jec(cell As String) As Variant
 Dim a As Variant, i As Long
 a = Split(cell)
 
 For i = UBound(a) To 0 Step -1
   If IsNumeric(a(0)) Then Exit For
   If IsNumeric(Replace(a(i), ",", "|")) Then
      a(i) = "~" & a(i) & "~"
      If i = UBound(a) Then Exit For
   End If
 Next
 jec = Split(Join(a), "~")
End Function

=jec(A2)

 

[JEC]

Map1
	A	B	C	D
2	1234 US HWY 10 SOUTH	1234 US HWY 10 SOUTH
3	A KIRKLY ST BEHIND 252	A KIRKLY ST BEHIND	252
4	MAXWELL RD EXT (SHOP) 845	MAXWELL RD EXT (SHOP)	845
5	MAXWELL RD EXT 843 BARN	MAXWELL RD EXT	843	BARN
6	SEASHIP LN 333 TLR #11	SEASHIP LN	333	TLR #11
7	ALFRED CIR 235 APT D-7	ALFRED CIR	235	APT D-7
8	BRAVO LN 700 LOT 421,432	BRAVO LN	700	LOT 421,432
9	SOBERWICK AVE 3632	SOBERWICK AVE	3632
10	KITTEN CROW RD REMOTE	KITTEN CROW RD REMOTE
11	US HWY 252 N (BLK BLDG) 3462	US HWY 252 N (BLK BLDG)	3462
Blad1

Cell Formulas
Range	Formula
B2,B10,B11:D11,B3:D9	B2	=jec(A2)