Split multiple groups of numbers with decimal points from a string

[SamJoy]

Hello Friends

I need help splitting a cell containing text, multiple groups of numbers, and decimal places.
e.g. if cell contains S = " Dagen gewerkt 21,00 195,00"
I need to separate the text part into one column, then 21.00 into another column, and 195.00 into another cell

I have multiple such columns to deal with.
Any help in this regard is highly appriciated.

 

[Kaper]

Natural solution in such situation would be by using RegEx (Regular Expressions).
Members of Insider program do have already access to such functions.
Unfortunately, standard Microsoft 365 users - not yet.

But we can use UDF (user defined Function) in VBA, and use it as normal function in the worksheet.
A code for such function (original source: General Purpose UDFs for using Regular Expressions in Excel ) could look like that:

Public Function RXMatches(Text As String, Pattern As String, Optional Group As Integer = 0, Optional IgnoreCase As Boolean = True) As Variant
    Dim retval() As String, i As Long
    ' from 
    
    ' Takes a string and returns all matches in a vertical array
    ' Text is the string to be searched
    ' Pattern is the regex pattern
    ' Group (optional) selects a parenthesized group (count the number of left parentheses preceding it to get the group number)
    ' IgnoreCase (optional) set to False for a case-sensitive search

    Dim RE As Object
    Dim Matches As Object

    Set RE = CreateObject("vbscript.regexp")
    RE.IgnoreCase = IgnoreCase
    RE.Global = True
    RE.Pattern = Pattern

    Set Matches = RE.Execute(Text)

    If (Matches.Count > 0) Then
        ReDim retval(0 To Matches.Count - 1)
        For i = 0 To Matches.Count - 1
            If (Group > 0) Then
                retval(i) = Matches(i).submatches(Group - 1)
            Else
                retval(i) = Matches(i)
            End If
        Next i
    Else
        ReDim retval(1)
        retval(0) = ""
    End If

    RXMatches = Application.Transpose(retval)
End Function

And the use in the worksheet would be:
=TRANSPOSE(RXMatches(A2,"([\D]+\s|\d*,\d*)"))

This string: "([\D]+\s|\d*,\d*)" defines a pattern for searching. If you want to have it explained - write it (without quotation marks) in "formula bar" on regex101: build, test, and debug regex and sample of your text in the window below

 

[hagia_sofia]

Hello, the example could probably be addressed via e.g. something like this:

=LET(
a,A1,
s,TEXTSPLIT(a," "),
t,ISNUMBER(--(s)),
HSTACK(TEXTJOIN(" ",,FILTER(s,t=FALSE)),--(FILTER(s,t=TRUE))))

But could you please upload a sample of the data because there may be more possible arrangements...

 

[Tom.Jones]

Hi,

Try TextToColumns

 

[Kaper]

With irregular names (containing spaces) and then numbers separeted by spaces TextToColumns tool will probably by not-so-easy to setup. Unfortunately, we got just one sample row of input data

 

[Tom.Jones]

Unfortunately, we got just one sample row of input data

My answer was for given row of data.

 

[Fluff]

Then maybe you missed this

I need to separate the text part into one column

Text to columns will not do that.

 

[Tom.Jones]

 

[Scott Huish]

With Dagen gewerkt 21,00 195,00 in A1
B1: =TRIM(LEFT(A1,MIN(IFERROR(FIND(SEQUENCE(10,,0),A1),""))-1))
C1: =TEXTSPLIT(TEXTAFTER(A1,B1)," ",,TRUE)

 

[SamJoy]

Natural solution in such situation would be by using RegEx (Regular Expressions).
Members of Insider program do have already access to such functions.
Unfortunately, standard Microsoft 365 users - not yet.

But we can use UDF (user defined Function) in VBA, and use it as normal function in the worksheet.
A code for such function (original source: General Purpose UDFs for using Regular Expressions in Excel ) could look like that:

Public Function RXMatches(Text As String, Pattern As String, Optional Group As Integer = 0, Optional IgnoreCase As Boolean = True) As Variant
    Dim retval() As String, i As Long
    ' from
  
    ' Takes a string and returns all matches in a vertical array
    ' Text is the string to be searched
    ' Pattern is the regex pattern
    ' Group (optional) selects a parenthesized group (count the number of left parentheses preceding it to get the group number)
    ' IgnoreCase (optional) set to False for a case-sensitive search

    Dim RE As Object
    Dim Matches As Object

    Set RE = CreateObject("vbscript.regexp")
    RE.IgnoreCase = IgnoreCase
    RE.Global = True
    RE.Pattern = Pattern

    Set Matches = RE.Execute(Text)

    If (Matches.Count > 0) Then
        ReDim retval(0 To Matches.Count - 1)
        For i = 0 To Matches.Count - 1
            If (Group > 0) Then
                retval(i) = Matches(i).submatches(Group - 1)
            Else
                retval(i) = Matches(i)
            End If
        Next i
    Else
        ReDim retval(1)
        retval(0) = ""
    End If

    RXMatches = Application.Transpose(retval)
End Function

And the use in the worksheet would be:
=TRANSPOSE(RXMatches(A2,"([\D]+\s|\d*,\d*)"))

This string: "([\D]+\s|\d*,\d*)" defines a pattern for searching. If you want to have it explained - write it (without quotation marks) in "formula bar" on regex101: build, test, and debug regex and sample of your text in the window below

Thank you so much for this. I will test this and update you. Once again Thank You Will try to play around this solution as i have many such columns with variations.