Separate Street Number from Street Name

[Blessy Clara]

Hi, Thank you for taking a moment to read this -

I would like to separate the street number from the street name
Criteria for Separation - From LEFT, Separate at NUMBER

Example

Wentworth Ave <tbody> </tbody>

Vil18 1 Monty Pl <tbody> </tbody>

Unt17/23/ Chave St <tbody> </tbody>

Unit82 98 Corinna St <tbody> </tbody>

8 Rischbieth Crs <tbody> </tbody>

13 Mt Warning Crs <tbody> </tbody>

45Cuthbert <tbody> </tbody>

Rm103 35 Bonney St <tbody> </tbody>

Rm1111 Carnegie Crs <tbody> </tbody>

<tbody>
</tbody>

Post Separation

	Wentworth Ave
Vil18 1	Monty Pl
Unt17/23	Chave St
Unit82 98	Corinna St
8	Rischbieth Crs
13	Mt Warning Crs
45	Cuthbert
Rm103 35	Bonney St
Rm1111	Carnegie Crs

<tbody>
</tbody>

 

[palaeontology]

if your sample data started in, let's say, D5, then in F5 enter (as an array formula ... ctrl, shift, enter) ... =LEFT(D5,MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},D5,ROW(INDIRECT("1:"&LEN(D5)))),0)))

in G5 enter (as an array formula ... ctrl, shift, enter) ... =RIGHT(D5,LEN(D5)-MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},D5,ROW(INDIRECT("1:"&LEN(D5)))),0)))

obviously drag down as far as you need.

Kind regards,

Chris

 

[Blessy Clara]

Hi Palaentology - Thank you for your reply -

Followed as per, but i am getting results as below

For Vlla216 Kerrigan St = in F Col - Vila21 and in Col G = 6 Kerrigan St
For Vlla12 33 Burkitt St = in F col = Vila 1 and Col G = 2 33 Burkitt St

The data has to be split where number Occurs - and also for those records that doesnt have a number t Col F/G is totally blank - please help me further if i am missing out anything. Thanks
(the data has been trimmed for space)

 

[Joe4]

I created my own User Defined Function in VBA to find out where the split is.

Function AddSplit(myEntry As Variant) As Integer

    Dim myLen As Long
    Dim i As Long
    
'   Find length of entry
    myLen = Len(myEntry)

'   Set initial value
    AddSplit = myLen

'   Handle null entry
    If myLen = 0 Then Exit Function
    
'   Loop through entry backwards
    For i = myLen To 1 Step -1
        If IsNumeric(Mid(myEntry, i, 1)) Then
            AddSplit = i
            Exit Function
        End If
    Next i
    
End Function

Then you can use this in a VBA procedure, or in Excel formulas right on the sheet.

Here is the formula method.
For an entry in cell A1, here is the formula to get the numeric portion:

=LEFT(A1,addsplit(A1))

and here is the formula for the rest:

=TRIM(RIGHT(A1,LEN(A1)-addsplit(A1)))

 

[admiral100]

Hi,

For the streen number:
=LEFT(A1,MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),0)))

For the street name :

=TRIM(MID(A1,MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),0))+1,999))

Ctrl+Shift+Enter Not just Enter

 

[palaeontology]

Hi Blessy,

I just tried my two array formulae on your initial list as well as the two new ones you mentioned (Kerrigan st etc) and I'm getting perfect results my end ... precise replicas of your 'post-separation' lists

Not sure why you're not getting the same.

Chris

Wentworth Ave			Wentworth Ave
Vil18 1 Monty Pl		Vil18 1	Monty Pl
Unt17/23/ Chave St		Unt17/23	Chave St
Unit82 98 Corinna St		Unit82 98	Corinna St
8 Rischbieth Crs		8	Rischbieth Crs
13 Mt Warning Crs		13	Mt Warning Crs
45Cuthbert		45	Cuthbert
Rm103 35 Bonney St		Rm103 35	Bonney St
Rm1111 Carnegie Crs		Rm1111	Carnegie Crs
Vlla216 Kerrigan St		Vlla216	Kerrigan St
Vlla12 33 Burkitt St		Vlla12 33	Burkitt St

<tbody>
</tbody>

But it sounds like you have some other options others have made.

Chris