CODEWITHSUNDEEP
excelexcel-formula
David
David

Reputation: 699

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

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.

Upvotes: 0

Views: 151

Answers (3)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

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

Using Basic Formulas

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)&"]")),"@"," ")

Using FILTERXML

Upvotes: 1

bosco_yip
bosco_yip

Reputation: 3802

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),"@"," "))

enter image description here

Upvotes: 1

navylover
navylover

Reputation: 13539

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

Upvotes: 0

Related Questions