How to remove letters from a cell that has numbers and letters together using VBA Script
How to remove only text from cells that containing numbers and text in Excel. In this video I used the VBA Macro which is the most powerful and flexible method, allowing you to customize the cleaning process based on more complex rules.
STEPS:
Follow the instructions and paste the script in the VBA module. Please remember the edit and insert the correct column and row numbers in the script before running it.
Open the VBA editor: Press Alt+F11.
Insert a new module: In the VBA editor, go to Insert, select Module.
Paste the following VBA code into the module:
VBA MACRO SCRIPT.
Sub ExtractNumbers_SpecificRange_WithDecimal()
Dim rng As Range
Dim cell As Range
Dim str As String
Dim i As Integer
Dim numStr As String
Dim hasDecimal As Boolean
' Set the range to Column D from row 3 to row 1569
Set rng = Range("B2:D1681")
For Each cell In rng
str = cell.Value
numStr = ""
hasDecimal = False ' Reset decimal flag for each cell
For i = 1 To Len(str)
Dim currentChar As String
currentChar = Mid(str, i, 1)
If IsNumeric(currentChar) Then
numStr = numStr & currentChar
ElseIf currentChar = "." And Not hasDecimal Then 'Allow one decimal point
numStr = numStr & currentChar
hasDecimal = True
End If
Next i
' Check if the resulting string is a valid number
If IsNumeric(numStr) Then
cell.Value = CDbl(numStr) ' Convert to a Double (number with decimals)
Else
cell.Value = "" ' Set to blank if it's not a valid number after cleaning
End If
cell.NumberFormat = "0.00" ' Format as two decimal places
Next cell
End Sub