Split numbers from strings in Excel

In the process of working with Excel, I have encountered some cases that require separating only the fraction in a given string, now this post shares with you how to do this. The following article details how to separate numbers from a given string in Excel.

There are 2 ways to separate numbers:

- Case 1: Separate numbers from series and get positive values.

- Case 2: Separate numbers from series and take both positive and negative values.

1. Separate numbers from series and get positive values

Step 1: Open the Excel File to decompose -> Press the key combination Alt + F11 -> VBA window appears -> Go to the Insert tab -> Module .

Split numbers from strings in Excel Picture 1

Step 2: At the command prompt, enter the following code:

Split numbers from strings in Excel Picture 2

 Function ExtractNumber (rCell As Range) Dim lCount As Long Dim sText As String Dim lNum As String sText = rCell For lCount = Len (sText) To 1 Step -1 If IsNumeric (Mid (sText, lCount, 1)) Then lNum = Mid (sText, lCount, 1) & lNum End If Next lCount ExtractNumber = CLng (lNum) End Function 

Note: Remember this function name because when returning to Excel File must use it.

Step 3: After importing, click Save (because it contains a macro, when saving, display a message box, choose OK) -> Return to Excel File to find the function as shown:

Split numbers from strings in Excel Picture 3

Step 4: Copy the formula for the remaining cells with the following results:

Split numbers from strings in Excel Picture 4

2. Separate numbers from the string and take both positive and negative values

Step 1: Open the Excel File to decompose -> Press the key combination Alt + F11 -> VBA window appears -> Go to the Insert tab -> Module .

Split numbers from strings in Excel Picture 5

Step 2: At the command prompt, enter the following code:

Split numbers from strings in Excel Picture 6

Private Function SuperTrim (TheStr As String) Dim Temp As String, DoubleSpase As String DoubleSpase = Chr (32) & Chr (32) Temp = Trim (TheStr) Temp = Replace (Temp, DoubleSpase, Chr (32)) Do Until InStr ( Temp, DoubleSpase) = 0 Temp = Replace (Temp, DoubleSpase, Chr (32)) Loop SuperTrim = Temp End Function Public Function Tach_So (strText As String) Dim strText_1 As String Dim subText () As String, so () As Double Dim i As Integer, j As Integer, k As Integer, m As Integer strText = SuperTrim (strText) subText = Split (strText, "") For i = 0 To UBound (subText) For j = 1 To Len (subText (i) ) k = 0 If IsNumeric (Mid (subText (i), j, 1)) _ Or (Mid (subText (i), j, 1) = "-" And IsNumeric (Mid (subText (i), j + 1 , 1))) Then k = j Exit For End If Next j If k <> 0 Then m = m + 1 strText_1 = Val (Mid (subText (i),k)) ReDim Preserve so (1 To m) As Double so (m) = strText_1 End If Next i If index> 0 And index <= m Then Tach_So = so (m) Else Tach_So = "" End If End Function

Note: Remember the function name "" Tach_so () ".

Step 3: Click Save, just select OK . Return to the Excel file and select the Tach_so () function as shown:

Split numbers from strings in Excel Picture 7

Step 4: Tach_so () function has only one parameter that is the number of cells to split.

Split numbers from strings in Excel Picture 8

Step 5: Copy the formula for the remaining cells we have the results:

Split numbers from strings in Excel Picture 9

Thus separating both positive and negative values. And the negative sign in the first position of the cell is obtained.

Note: The above functions only apply to sequences that contain 1 string of consecutive numbers. If the data is a series of different numerical characters separated by alphabetic characters, the return value is the first numeric string.

Good luck!