Remove Leading and Trailing Numbers from Excel Cells with VBA
Question details
Remove numbers from the beginning and end of Excel cell values while keeping the text between them, such as converting "22234 Hello World 7923" to "Hello World".
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Clean a selected range containing text surrounded by unwanted numeric prefixes and suffixes.
- Observed behavior
- The desired result retains the middle text, removes boundary digits, and trims surrounding spaces without removing numbers embedded within the text.
Use desktop Excel, since Excel for the web cannot run VBA macros, and enable the Developer tab if it is hidden. Identify the cells to clean and test on a small copied range first because the macro replaces cell values and generally cannot be undone with Excel's Undo command.
Run a VBA macro to strip digits from both ends of selected cells
Use this method to clean multiple cells while preserving text and any numbers inside that text.
The macro removes digits 0–9 from each boundary, trimming ordinary spaces after each removal. It skips formulas and error values, and does not change internal spaces or numbers.
For example, "22234 Hello World 7923" becomes "Hello World", while "123 Room 42 West 987" becomes "Room 42 West".
In desktop Excel, go to Developer > Visual Basic. If Developer is not visible, enable it in Excel's ribbon customization settings.
In the VBA editor, select your workbook in Project Explorer, then choose Insert > Module. Paste this code: Sub RemoveBoundaryNumbers() Dim cell As Range Dim text As String If TypeName(Selection) <> "Range" Then Exit Sub For Each cell In Selection.Cells If Not cell.HasFormula Then If Not IsError(cell.Value2) Then text = Trim$(CStr(cell.Value2)) Do While Len(text) > 0 If Left$(text, 1) Like "[0-9]" Then text = Trim$(Mid$(text, 2)) Else Exit Do End If Loop Do While Len(text) > 0 If Right$(text, 1) Like "[0-9]" Then text = Trim$(Left$(text, Len(text) - 1)) Else Exit Do End If Loop If text <> Trim$(CStr(cell.Value2)) Or text <> CStr(cell.Value2) Then cell.Value2 = text End If End If End If Next cell End Sub
Return to the worksheet and select only the populated cells you want to clean. Go to Developer > Macros, select RemoveBoundaryNumbers, and click Run.
Check that a value such as "22234 Hello World 7923" now reads "Hello World". To retain the macro, choose File > Save As and save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).
Review and edit cleaned Excel worksheets in WPS Spreadsheet
WPS Office is a free, lightweight, easy-to-use alternative for everyday spreadsheet work. Open your cleaned Excel workbook in WPS Spreadsheet with familiar editing tools and Excel format compatibility for a smooth transition. This recommendation does not replace the desktop Excel VBA workflow or guarantee support for the macro.
Frequently Asked Questions
Will the macro remove numbers in the middle of the text?
No. It checks only the beginning and end of each selected cell value. For example, "123 Room 42 West 987" becomes "Room 42 West". However, a number at the end of the remaining text is still a boundary number and will be removed.
Can I use Power Query instead of VBA?
Yes, if every value follows a consistent leading-numbers, text, trailing-numbers pattern. Select the data and choose Data > From Table/Range. In Power Query, use Split Column > By Digit to Non-Digit and By Non-Digit to Digit as appropriate, retain the middle text column, and apply Transform > Format > Trim. Internal numbers can introduce additional splits, so inspect the results before choosing Home > Close & Load.
Does this macro change cells containing formulas?
No. The macro checks HasFormula and skips formula cells. If you need to clean formula results, copy them to a separate range using Paste Special > Values, then select that range and run the macro.
Why are decimal points or minus signs left behind?
The macro removes individual digits 0–9, not complete numeric tokens. A minus sign or decimal point stops the boundary scan. Values such as "-123 Hello 45.6" need a different rule that explicitly handles signs and decimal separators.
Can I run this VBA macro in Excel for the web?
No. Open the workbook in desktop Excel, then use Developer > Visual Basic to insert the code and Developer > Macros to run it. Save as .xlsm if you want to keep the macro in the workbook.
