To identify the position of the first alphabetic letter in a string and take all values before that letter

[AnTIW]

Hello,
I am trying to implement within a macro blow how to identify the position of the first alphabetic letter a string and take all values before that letter.
This is a part the macro below:
If xsplit Like "*,*" Then
For I = 1 to 100
'line below where I want to do that
Sheets("x").Cells(I,1) = Trim(Split(xsplit, ",")(I-1))
Next I

Example of the data in one cell:
1234 5679123 data sample, 123456789 data, 123456 12345678 data sample,

Thank you so much for your help

 

[Peter_SSs]

Welcome to the MrExcel board!

Example of the data in one cell:
1234 5679123 data sample, 123456789 data, 123456 12345678 data sample,

And the expected result(s) from that data sample?

 

[AnTIW]

Welcome to the MrExcel board!

And the expected result(s) from that data sample?

Expected results would be extracted to a different sheet and split in to these:
1234 5679123
12345689
123456 12345678

Thank you for your help

 

[MickG]

Try this for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG14Aug03
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Trim(Dn.Value), " ")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
            [COLOR="Navy"]If[/COLOR] Not IsNumeric(Left(Sp(n), 1)) [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] For
                nStr = nStr & IIf(nStr = "", Sp(n), " " & Sp(n))
        [COLOR="Navy"]Next[/COLOR] n
        c = c + 1
       ray(c) = nStr: nStr = ""
[COLOR="Navy"]Next[/COLOR] Dn
  Sheets("Sheet2").Range("A1").Resize(c).Value = Application.Transpose(ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[AnTIW]

Try this for results on sheet2.

[COLOR=navy]Sub[/COLOR] MG14Aug03
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    Sp = Split(Trim(Dn.Value), " ")
        [COLOR=navy]For[/COLOR] n = 0 To UBound(Sp)
            [COLOR=navy]If[/COLOR] Not IsNumeric(Left(Sp(n), 1)) [COLOR=navy]Then[/COLOR] [COLOR=navy]Exit[/COLOR] For
                nStr = nStr & IIf(nStr = "", Sp(n), " " & Sp(n))
        [COLOR=navy]Next[/COLOR] n
        c = c + 1
       ray(c) = nStr: nStr = ""
[COLOR=navy]Next[/COLOR] Dn
  Sheets("Sheet2").Range("A1").Resize(c).Value = Application.Transpose(ray)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thank you for your help, Mick.

Would It be possible to implement that in my Split Macro below?

 Sub Splitx()
        
        Dim AccSplit As String
        Dim c As Variant
        Dim i As Long
        
        
        Sheets("x").Range("A1:B1000").ClearContents
        
        
        xSplit = Sheets("Data").Range("A1").Value
        
        On Error Resume Next
        If xSplit Like "*,*" Then
            
          For i = 1 To 100
          'line below where I want to identify the position of the  first alphabetic letter in a string and take all values before that letter
                Sheets("x").Cells(i, 1) = Trim(Split(xSplit, ",")(i - 1))
                Next i
            End If
            On Error GoTo 0
            For Each c In Sheets("x").Range("A1:A1000")
                If c.Value = "" Then Exit For
                
                
                On Error GoTo InvalidData
                c.Offset(0, 1).Value = Trim(Mid(c.Value, InStr(c.Value, " ")))
                c.Offset(0, 0).Value = "'" & Trim(Left(c.Value, InStr(c.Value, " ")))
                
                If Mid(c.Value, 7, 1) = " " Then c.Value = Replace(c.Value, " ", "/")
                GoTo SkipMe
InvalidData:
                c.Offset(0, 1).Value = c.Value
                c.Offset(0, 1).Value = c.Value
SkipMe:
                Next c
                
            End Sub

 

[Peter_SSs]

For data in column A of Sheet1, starting at row 2, try this in a copy of your workbook.
Also assumes that Sheet2 exists and has nothing in column A from row 2.

Sub ExtractParts()
  Dim RX As Object
  Dim a As Variant, itm As Variant, m As Variant
  Dim Result As String
  Dim k As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\d[ \d]*(?=[a-zA-Z]|$)"
  With Sheets("Sheet1")
    a = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Value
  End With
  For Each itm In a
    For Each m In RX.Execute(itm)
      Result = Result & "|" & Trim(m)
      k = k + 1
    Next m
  Next itm
  Sheets("Sheet2").Range("A2").Resize(k).Value = Application.Transpose(Split(Mid(Result, 2), "|"))
End Sub

 

[Peter_SSs]

Thank you for your help, Mick.

Perhaps I'm not understanding. Mick's code didn't produce those expected results for me for your sample data.
You did say that "1234 5679123 data sample, 123456789 data, 123456 12345678 data sample" was all in one cell didn't you?

 

[AnTIW]

Perhaps I'm not understanding. Mick's code didn't produce those expected results for me for your sample data.
You did say that "1234 5679123 data sample, 123456789 data, 123456 12345678 data sample" was all in one cell didn't you?

That is correct, Peter. All of that data is in one cell. I will try to use your macro now.

Thanks

 

[MickG]

I see I misread your requirement !!!!!

 

[AnTIW]

I tried to make your code work, but I failed.
My knowledge of VBA is really basic...
Would it be possible for one of you to try to implement it in the script that I posted above?

Im sorry for lack of knowledge in advanced VBA