How to split cell contents from the "", remove "x" and be left with just numbers-CodePudding

I am trying to split up a cell's contents, but am getting stuck. The contents of the cell will be:

Sample_1 10x

Sample_2 5x

Sample_3 100x

I need to remove the contents except for the numbers before the "x". These numbers will show up in another cell and then used for calculations.

Originally, I hade my code as:

       For Each unit In Range("SD")
           If unit.Offset(0, -19).Value <> "Unknown" Then
               unit.Value = "N/A"
           End If
           If unit.Offset(0, -19).Value = "Unknown" Then
                If unit.Offset(0, -20).Value Like "*Sample*" Then
                    unit.Value = Left(Right(unit.Offset(0, -20), 3), 2)
                End If
           End If

However, this formula only works for 1 or 2 digit numbers before the "x", not 3 digits. Is there a way to split the contents where the space is, remove the "x" from the contents, and leave the number?

Ex.

"Sample_1 10x"

Split where the space is and leave 2nd half: "10x"

Remove the "x"

Left with "10"

CodePudding user response：

You could use the following formula

=MID(A1,(FIND(" ",A1) 1),LEN(A1)-FIND(" ",A1)-1)

Or, with your data in cells A1, A2 and A3, the following code displays a message box that contains 10, then 5 and then 100

Sub a()

With Sheets(1).Range("A1")

    For i = 0 To .CurrentRegion.Rows.Count - 1

        StrData = .Offset(i, 0)

        StrData = Left(StrData, Len(StrData) - 1)

        MsgBox Mid(StrData, InStr(StrData, " ")   1)

    Next i

End With

End Sub

CodePudding user response：

You can use Split and Val:

For Each unit In Range("SD")
    If unit.Offset(0, -19).Value <> "Unknown" Then
        unit.Value = "N/A"
    End If
    If unit.Offset(0, -19).Value = "Unknown" Then
        If unit.Offset(0, -20).Value Like "*Sample*" Then
            unit.Value = Val(Split(unit.Offset(0, -20).Value & " ", " ")(1))
        End If
    End If
Next