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 [TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 278"]
<tbody>[TR]
[TD="width: 278"]Wentworth Ave[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 278"]
<tbody>[TR]
[TD="width: 278"]Vil18 1 Monty Pl[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 278"]
<tbody>[TR]
[TD="width: 278"]Unt17/23/ Chave St[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 278"]
<tbody>[TR]
[TD="width: 278"]Unit82 98 Corinna St[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 278"]
<tbody>[TR]
[TD="width: 278"]8 Rischbieth Crs[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 278"]
<tbody>[TR]
[TD="width: 278"]13 Mt Warning Crs[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 278"]
<tbody>[TR]
[TD="width: 278"]45Cuthbert[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 278"]
<tbody>[TR]
[TD="width: 278"]Rm103 35 Bonney St[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 278"]
<tbody>[TR]
[TD="width: 278"]Rm1111 Carnegie Crs[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Post Separation
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Wentworth Ave[/TD]
[/TR]
[TR]
[TD]Vil18 1[/TD]
[TD]Monty Pl[/TD]
[/TR]
[TR]
[TD]Unt17/23[/TD]
[TD]Chave St[/TD]
[/TR]
[TR]
[TD]Unit82 98[/TD]
[TD]Corinna St[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Rischbieth Crs[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Mt Warning Crs[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]Cuthbert[/TD]
[/TR]
[TR]
[TD]Rm103 35[/TD]
[TD]Bonney St[/TD]
[/TR]
[TR]
[TD]Rm1111[/TD]
[TD]Carnegie Crs[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="width: 453"]
<tbody>[TR]
[TD]Wentworth Ave[/TD]
[TD][/TD]
[TD][/TD]
[TD]Wentworth Ave[/TD]
[/TR]
[TR]
[TD]Vil18 1 Monty Pl[/TD]
[TD][/TD]
[TD]Vil18 1[/TD]
[TD]Monty Pl[/TD]
[/TR]
[TR]
[TD]Unt17/23/ Chave St[/TD]
[TD][/TD]
[TD]Unt17/23[/TD]
[TD]Chave St[/TD]
[/TR]
[TR]
[TD]Unit82 98 Corinna St[/TD]
[TD][/TD]
[TD]Unit82 98[/TD]
[TD]Corinna St[/TD]
[/TR]
[TR]
[TD]8 Rischbieth Crs[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]Rischbieth Crs[/TD]
[/TR]
[TR]
[TD]13 Mt Warning Crs[/TD]
[TD][/TD]
[TD]13[/TD]
[TD]Mt Warning Crs[/TD]
[/TR]
[TR]
[TD]45Cuthbert[/TD]
[TD][/TD]
[TD]45[/TD]
[TD]Cuthbert[/TD]
[/TR]
[TR]
[TD]Rm103 35 Bonney St[/TD]
[TD][/TD]
[TD]Rm103 35[/TD]
[TD]Bonney St[/TD]
[/TR]
[TR]
[TD]Rm1111 Carnegie Crs[/TD]
[TD][/TD]
[TD]Rm1111[/TD]
[TD]Carnegie Crs[/TD]
[/TR]
[TR]
[TD]Vlla216 Kerrigan St[/TD]
[TD][/TD]
[TD]Vlla216[/TD]
[TD]Kerrigan St[/TD]
[/TR]
[TR]
[TD]Vlla12 33 Burkitt St[/TD]
[TD][/TD]
[TD]Vlla12 33[/TD]
[TD]Burkitt St[/TD]
[/TR]
</tbody>[/TABLE]

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

Chris