Excel Help: Split Cell by removing any digits value

[Lego15]

Hello,
I have a sheet of data and I would like to remove all value starting by a digit in each cell:
Example:

A1= Minute Maid Orange Juice 11 fl oz bottle.
A2= Diet coke 10 oz bottle

Need result as follow:

A1= Minute Maid Orange Juice B1= 11 fl oz bottle
A2= Diet Coke B2= 10 oz bottle

What is the best way to do this?

Thank you!

 

[byundt]

You might try using regular expressions to solve the problem. The pattern \d+ is specific for one or more digits.

The function below returns a two-element array split at the first number that is found in sText. You can test the code using an array formula on the worksheet like:
=SplitAtNumber(A1) 'Return results to B1 and C1
=Transpose(SplitAtNumber(A1)) 'Return results to B1 & B2

You may want to Trim the strings before returning them--otherwise you get trailing spaces. I commented out a statement for that.

The example below uses late-binding, so for effiency you may want to use a sub that loops through your data. That way you only create the RgExp object once.

Function SplitAtNumber(sText As String) As Variant
Dim RgExp As Object
Dim s1 As String, s2 As String
Dim i As Integer
Set RgExp = CreateObject("VBScript.RegExp")
With RgExp
    .Pattern = "\d+"
    s1 = sText
    If .test(sText) Then
        i = .Execute(sText)(0).FirstIndex
        If i = 0 Then
            s1 = ""
            s2 = sText
        Else
            s1 = Left(sText, i - 1)
            s2 = Mid$(sText, i)
        End If
    End If
End With
SplitAtNumber = Array(s1, s2)
Set RgExp = Nothing
End Function

Brad

 

[SkipVought]

...or use the split function

Function SplitAtNumber(sText As String, Optional FirstOrSecond As Integer = 1) As Variant
   Dim a, i As Integer, bFoundNumber As Boolean
   bFoundNumber = False
   a = Split(sText, " ")
   For i = 0 To UBound(a)
       If IsNumeric(Left(a(i), 1)) Then bFoundNumber = True
       If FirstOrSecond = 1 Then
            If bFoundNumber Then
                  Exit For
            End If
            SplitAtNumber = SplitAtNumber & a(i) & " "
       Else
            If bFoundNumber Then
                SplitAtNumber = SplitAtNumber & a(i) & " "
            End If
       End If
   Next
   SplitAtNumber = Left(SplitAtNumber, Len(SplitAtNumber) - 1)
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Lego15]

Thank you for your help here!