How to extract last number from text string in Excel?
Supposing, you have a list of text strings which mixed with texts and numbers, now, you need to extract the last set of numbers as following screenshot shown. How could you handle this job in Excel?
To quickly extract the last set of numbers from text strings, you can apply the following VBA code. Please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, then copy and paste the following code in the Module Window.
VBA code: Extract last numbers from text strings:
Sub GetLastDigits() 'Updateby Extendoffice Dim xRg As Range Dim xCell As Range Dim xRegEx As Object Dim xRetList As Object Dim xAddress As String On Error Resume Next xAddress = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Please select the range:", "Kutools for Excel", xAddress, , , , , 8) If xRg Is Nothing Then Exit Sub If xRg.Columns.Count > 1 Then MsgBox "Only one column can be available", vbInformation, "Kutools for Excel" Exit Sub End If Set xRegEx = CreateObject("VBSCRIPT.REGEXP") With xRegEx .MultiLine = False .Global = True .IgnoreCase = True .Pattern = "(\d+)" End With For Each xCell In xRg Set xRetList = xRegEx.Execute(xCell.Value) If xRetList.Count > 0 Then If Left(xRetList(xRetList.Count - 1), 1) = 0 Then xCell.Offset(0, 1) = Right(xRetList(xRetList.Count - 1), Len(xRetList(xRetList.Count - 1)) - 1) Else xCell.Offset(0, 1) = xRetList(xRetList.Count - 1) End If End If Next End Sub
3. Then press F5 key to run this code, and a prompt box is appeared to remind you to select the data range that you want to use, see screenshot:
4. And then click OK button, and the last set of numbers have been extracted from each text strings into the adjacent column cells, see screenshot:
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 months agoI'm sure it will be possible.
- To post as a guest, your comment is unpublished.· 2 months agohow do that without using vba?
- To post as a guest, your comment is unpublished.· 2 months agoHi,
Sorry, maybe there is no other direct method for getting the result. If anyone have the solution, please comment here.