Thread: Text to Column with Text & Varying-length Numbers

I am trying to separate a string into two columns, however, the string contains text and numbers and it does not have an easy deliminator.

Here is an example of the string data:
Antioxidant Rich Fruit Blends. 6 months and up4.00 oz
4.22 oz
10.00 oz
Snap on lid 3.5 oz - 2 pack7.00 oz
No artificial flavors. Microwavable.45.00 oz

This is what I'm trying to get it to look like:

A1: Antioxidant Rich Fruit Blends. 6 months and up   B1: 4.00 oz
A1:                                                  B1: 4.22 oz
A1:                                                  B1: 10.00 oz
A1: Snap on lid 3.5 oz - 2 pack                      B1: 7.00 oz
A1: No artificial flavors. Microwavable.             B1: 45.00 oz

The only consistent aspect is the "oz" at the end, however, as you can see sometimes "oz" appears in the middle of the string.

I have tried LEFT/RIGHT/Mid, however, since the numbers I want in column B vary in length from 4 characters to 6, LEFT/RIGHT/MID ends up cutting something wrong.

Thanks for your help!

After giving this problem a little space, I was able to figure out a solution.

=MID(A1,IF(LEN(A1)<10,1,MIN(FIND({"0.",1,2,3,4,5,6,7,8,9},A1&"0.123456789",SEARCH("oz",A1,LEN(A1)-3)-8))),12)

The key to this problem is figuring out where to tell the MID function to start. So, the MIN(FIND function finds the first occurrence of a number 1 through 9, but instead of starting to look for numbers at the beginning of the cell we find the last "oz" and back up 8 characters.

I hope this helps someone.

Hi you can try this and see if it works for you.

Sub TestSplit()

' Notes
'------------------------------------------------------------------
' This only where there is "OZ" at the end of the line to be split.
' It works backwards from find the "OZ" and checks each character to see if it is numeric, blank or a decimal point.
' It allows the presents of 1 decimal point in look backwards.
' It ignores spaces until a letter is detected or the second decimal point.
' Places output in column C
' this does not remove or add any spaces between numbers and "OZ"
'-------------------------------------------------------------------
Dim OZPosition As Integer 'the counting Left to right where the last "OZ" occurs
Dim CurrentCHAR As Variant

Dim Strlength As Integer ' length of the text in A column
Dim FLG As Boolean 'Flag used to detect first decimal point look backwards in the string.
Dim rowSTART As Long 'Start Row
Dim rowEND As Long ' End row

CurrentCHAR = ""
OZPosition = 0


Range("B:C").Clear 'Optional... clear values in Columns B and C

' ENTER Number of rows to do this on.
rowSTART = InputBox("Enter Start Row number", "START ROW NUMBER")
rowEND = InputBox("Enter End Row number", "END ROW NUMBER")


For J = 1 To rowEND ' used to cycle through the rows using variable J


'set default values for each row
ozp = 0
FLG = False 'set to false for start of eachsearch in the string
CurrentCHAR = "´"
OZPosition = 0
Strlength = Len(Range("A" & J).Text)
OZPosition = InStrRev(UCase(Range("A" & J)), "OZ") 'look backwards in the string for "OZ"

    If OZPosition >= 0 Or Len(Range("A" & J)) Then     'Abort if the Postion of " OZ" is less than 0 or cell is empty
    
                For ozp = OZPosition - 1 To 1 Step -1 'cycle backwards through the characters from the postion of Oz in the value from column A
                
                  
                
                CurrentCHAR = Mid(Range("A" & J), ozp, 1) ' Get the character you want to check.
               
                
                      
                    If FLG = True And CurrentCHAR = "." Then
                        Cells(J, "C").Value = Right(Range("A" & J), OZPosition - ozp + 1)
                    
                        Exit For ' exit search if a second ¨.¨is found
                    Else
                                If CurrentCHAR = " " Or CurrentCHAR = "." Then
                                    
                                    Cells(J, "C").Value = Right(Range("A" & J), OZPosition - ozp)
                                    
                                    If CurrentCHAR = "." Then FLG = True ' FLG true if decimal is detected.
                                           
                                Else
                                                    If IsNumeric(CurrentCHAR) Then
                                                        
                                                        Cells(J, "C").Value = Right(Range("A" & J), OZPosition - ozp + 2)
                                                        
                                                    Else
                                                        
                                                        Cells(J, "C").Value = Right(Range("A" & J), OZPosition - ozp + 1)
                                                        
                                                    Exit For ' Exit loop if a non-numeric is detected.
                                                    End If
                                End If
                                    
                        End If
                      
                            Debug.Print ozp & "  C VALUE " & Cells(J, "C").Value
                    
                
                Next ozp
    Else
    End If

            
Next J


End Sub

Select the First cell containing data and run macro "TxtToCol" below.Pl convey your result.
Thanks in advance.

Sub TxtToCol()
'Select the starting cell that contains data and run macro

Dim k, k1, k2 as string
Dim R_ofset, L as integer

R_ofset = 0
Do While ActiveCell.Offset(R_ofset, 0).Value <> ""

k = Trim(ActiveCell.Offset(R_ofset, 0).Value)
L = Len(k)
For T = L - 6 To 1 Step -1
M = IsNumeric(Mid(k, T, 1))

If M = False Then
k1 = Left(k, T)
k2 = Right(k, L - T)
ActiveCell.Offset(R_ofset, 0).Value = k1
ActiveCell.Offset(R_ofset, 1).Value = k2
Exit For
End If

If T = 1 Then
ActiveCell.Offset(R_ofset, 0).Value = ""
ActiveCell.Offset(R_ofset, 1).Value = k
End If

Next T
R_ofset = R_ofset + 1
Loop
End Sub

Another way: