Split The text & Numbers

[Abhijeet]

Hi
I want split the text & numbers from String please tell me how to do this

 

[Luke M]

This is not an easy thing to accomplish, but this thread offers several solutions, both formula and UDF.
http://dailydoseofexcel.com/archive...the-nth-numerical-element-without-delimiters/

 

[Marc L]

Hi,

according to the sample workbook,
this code works only with one letter text :​

Sub Demo1()
    With Cells(1).CurrentRegion
        .Cells(2, 2).Resize(.Rows.Count - 1, .Columns.Count - 1).Clear
    End With

    B& = 1:  C& = 1

    For R& = 2 To Cells(1).CurrentRegion.Rows.Count
        For Each V In Split(Application.Trim(Cells(R, 1).Value))
            Do
                If Val(V) Then
                    C = C + 1
                    Cells(C, 3).Value = Val(V)
                    V = Replace(V, Replace(Val(V), ",", "."), "")
                Else
                    B = B + 1
                    Cells(B, 2).Value = Left$(V, 1)
                    V = Mid$(V, 2)
                End If
            Loop Until V = ""
        Next
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Abhijeet]

Thanks Mark L This is work fine

 

[Marc L]

An alternative, and regardless of the number of letters !
♪♫ Better, Faster, Smarter, … ♪♫​

Sub Demo2()
    Dim oSre As Object, oRes As Object
    Set oSre = CreateObject("VBScript.RegExp")
        oSre.IgnoreCase = True:  oSre.Pattern = "[A-Z]+":  B& = 1:  C& = 1
    [B2].Resize([A1].CurrentRegion.Rows.Count - 1, 2).Clear

    For R& = 2 To [A1].CurrentRegion.Rows.Count
        For Each V In Split(Application.Trim(Cells(R, 1).Value))
            Set oRes = oSre.Execute(V)
             If oRes.Count Then B = B + 1: Cells(B, 2).Value = oRes(0): V = Replace(V, oRes(0), "")
                 If V > "" Then C = C + 1: Cells(C, 3).Value = V
        Next
    Next

    Set oRes = Nothing:  Set oSre = Nothing
End Sub

You like ? So thanks …
​

 

[Abhijeet]

Thanks For this