Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Help: Split Cell by removing any digits value

Status
Not open for further replies.

Lego15

Technical User
Dec 6, 2008
9
US
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!
 
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.
Code:
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

 
...or use the split function
Code:
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top