This function replaces all occurences of numbers in the input string with '
#' and returns the replaced string. It recognizes integers, floating point, and negative numbers. It will not replace digits that are actually part of a word (eg: '
var5' or '
level9code'). Special care is taken for numbers in formats like '
=45'.
The function keeps scanning the string for " " (a space) and then extracts the word before it. If the word is numeric, it replaces that word with '#'. Special care is taken for last word which doesn't have a space at its end.
'Replaces all occurences of numbers from a string with "#"
Function ReplaceNumbers(sample As String) As String
Dim fromx As Integer
Dim pos As Long
Dim tmp As String
fromx = 1
Do While fromx < Len(sample)
'To recognise numbers of the form "=45"
If Mid(sample, fromx, 1) = "=" Then fromx = fromx + 1
pos = InStr(fromx, sample, " ", vbTextCompare)
If pos <> 0 Then
tmp = Mid(sample, fromx, pos - fromx)
If IsNumeric(tmp) Then
sample = Replace(sample, tmp, "#", 1, 1, vbTextCompare)
End If
Else
Exit Do
End If
fromx = pos + 1
Loop
'For last word
If Mid(sample, fromx, 1) = "=" Then fromx = fromx + 1
tmp = Mid(sample, fromx, Len(sample))
If IsNumeric(tmp) Then
sample = Replace(sample, tmp, "#", 1, 1, vbTextCompare)
End If
ReplaceNumbers = sample
End Function
