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:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!