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 StringDim fromx As IntegerDim pos As LongDim tmp As Stringfromx = 1Do While fromx < Len(sample)'To recognise numbers of the form "=45"If Mid(sample, fromx, 1) = "=" Then fromx = fromx + 1pos = 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 IfElse Exit DoEnd Iffromx = pos + 1Loop'For last wordIf Mid(sample, fromx, 1) = "=" Then fromx = fromx + 1tmp = Mid(sample, fromx, Len(sample))If IsNumeric(tmp) Then sample = Replace(sample, tmp, "#", 1, 1, vbTextCompare)End If ReplaceNumbers = sampleEnd Function
