How to replace characters in a string using VBA in Excel for Mac [closed]

1
votes

I have a column filled with alphanumeric strings. Some of strings have long integers embedded in them. How do I replace the integers with a string of 'x' characters? For example:

  • "this is account 123456789" becomes "this is account xxxxxxxxx"
  • "please return item 999999999999 to storage" becomes "please return item xxxxxxxxxxxx to storage"

Note that:

  • the digits can occur anywhere in the string including start, end or mid.
  • the long integers are either 9-chars or 12-chars long

I am using Excel for Mac, V16. Since it is Mac, I do not have access to a RegExp object.

excelregexvba
What have you tried? a simple loop doing a search and replace should work - cybernetic.nomad
This would be too slow. - DatsunBing
100k strings processed in less than a second is too slow? How much data do you have, and how fast does it need to be? - Tim Williams
Loop over the string and check if Mid(s, i, 1) Like "#" - if yes then record the start position and keep looping till you hit a non-number character, then check the run length - if it's 9 or 12 than use (eg) Mid(s, pos, runLength) = String(runLength, "X") - Tim Williams