Looking for a way to split cells in excel based the last five numbers in a cell

Question

I am looking for a way to fix a mixed data set representing addresses in Excel.

Here is an example dataset.

Kirchgasse 2389179 Beimerstetten

All this should result in three datasets.

A1 = Kirchgasse 2389179 Beimerstetten
A2 = Kirchgasse 23
A3 = 89179
A4 = Beimerstetten

Pattern is, that A3 is always the last 5 numbers in the dataset.

I am totally lost here, as Excel is usually not my weapon of choice.

Have a look at this, but look for the series of numbers : https://stackoverflow.com/a/70906018/4961700 not doing it as it is just a variation... — Solar Mike, Feb 06 '22 at 08:34
What formula have you tried? Please share no matter if it's faulty. Also, what version of Excel? Also, do you have more sample data, because typically addresses are all very different. — JvdV, Feb 06 '22 at 09:11
Will your address always have three words, with the digits being the second word? If not, a more relevant data set might be useful to craft a formula solution. — Ron Rosenfeld, Feb 06 '22 at 10:25

score 1 · Answer 1 · answered Feb 06 '22 at 12:01

1

In A2, formula copied down :

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(" "&REPLACE(REPLACE(A$1,AGGREGATE(14,6,FIND(ROW($1:$10)-1,A$1,COLUMN(A:Z)),1)-4,0," "),FIND(" ",A$1),1,"@")," ",REPT(" ",50)),ROW($A1)*50,50),"@"," "))

answered Feb 06 '22 at 12:01

bosco_yip

3,762
2
5
10

score 1 · Accepted Answer · answered Feb 06 '22 at 12:12

You can use Excel Formulas as well, to split cells, Excel gives us ample opportunity to use the features, there are lot of ways for a query to resolve, you can follow any of the following approaches, to accomplish the desired output

To Extract The First Part From Cell A1, Formula Used In Cell A2

=LEFT($A1,FIND(" ",$A1)+2)

To Extract The Second Part From Cell A1, Formula Used In Cell A3

=LEFT(REPLACE($A1,1,LEN($A2),""),5)

To Extract The Last Part Of The String From Cell A1, Formula Used In Cell A4

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

However To Extract The 5 Numbers, you can use any one of the following as well that suits with your Excel Version

Formula shown in cell A11

=MAX(IFERROR(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1))),5)*1,""))

Formula shown in cell A13

=LARGE(IFERROR(--MID($A1,SEQUENCE(LEN($A1)),COLUMN($A$1:$E$1)),0),1)

Formula shown in cell A15

=AGGREGATE(14,6,IFERROR(--MID($A1,ROW($1:$10000),COLUMN($A$1:$E$1)),0),1)

Formula shown in cell A17

=--RIGHT(FILTERXML("<p><w>"&SUBSTITUTE($A1," ","</w><w>")&"</w></p>","//w[.*0=0]"),5)

To get a detailed knowledge on FILTERXML Function you can refer the link below, prepared by JvdV Sir

FILTERXML FUNCTION DETAILED ANALYSIS BY JvdV Sir

Here is few more ways

Formula used in cell A2

=SUBSTITUTE(TRIM(FILTERXML("<p><w>"&SUBSTITUTE(SUBSTITUTE(LEFT($A1,FIND(" ",$A1)+2)&REPLACE($A1,1,FIND(" ",$A1)+2," ")," ","@",1)," ","</w><w>")&"</w></p>","//w")),"@"," ")

Just enter the formula in cell A2, and it will spill if you are using O365 or Excel 2021

And if you are not using the above Excel Version then you may try this and Fill Down The Formula

Formula Used In Cell B2 & Fill Down

=SUBSTITUTE(TRIM(FILTERXML("<p><w>"&SUBSTITUTE(SUBSTITUTE(LEFT($A$1,FIND(" ",$A$1)+2)&REPLACE($A$1,1,FIND(" ",$A$1)+2," ")," ","@",1)," ","</w><w>")&"</w></p>","//w["&ROW(A1)&"]")),"@"," ")

score 0 · Answer 3 · answered Feb 06 '22 at 09:27

Put Kirchgasse 2389179 Beimerstetten in the A1 cell, and use the below VBA codes to set the D1 cell value as the last 5 numbers

Sub Test_Pattern()
    
    Dim stringOne   As String
    Dim regexOne    As Object
    
    Set regexOne = New RegExp
    
    regexOne.Pattern = "(\d{5})(?!.*\d)"
    regexOne.Global = TRUE
    regexOne.IgnoreCase = IgnoreCase
    stringOne = Range("A1").Value
    
    Set theMatches = regexOne.Execute(stringOne)
    
    For Each Match In theMatches
        Range("D1").Value = Match.Value
    Next
    
End Sub

To learn how to use regex in VBA, see this ref

Looking for a way to split cells in excel based the last five numbers in a cell

3 Answers3