How to replace any 7 digit number at a random position in a cell

Hey there,


I tried to solve my problem by going through an endless number of existing threads but unfortunately nothing I found was quite what I was looking for.


THE PROBLEM:
I have a table with cells that contain mostly text in varying length. Somewhere in those cells, every now and then, in the middle of all the text there is one or more 7 digit numbers. I need to replace these numbers with XXXXXXX. Sounds simple, right? But I have no idea how...


I am sure it is somehow possible with the right combination of IF, MID, LEFT, LOOKUP etc but I just couldn't it figure out.


HELP!

eskimo,

Welcome to the forum!

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

If you're looking for just a few specific sets of numbers, you can use Ctrl+H. If not, it might help to post a dummy workbook letting us know your exact layout. Would the numbers always be 7 digits?

eskimo,

Attached is a modified version of you sample workbook. It contains a button named "Scrub Numbers". That button is assigned to the following macro:

Please Login or Register  to view this content.

To view macros in a workbook, use keyboard shortcut Alt+F11. The macro will take all 7-digit numbers in column A starting in row 2 (I used row 1 for the header) and replace them with "xxxxxxx"

try more func addin then use something like
=REGEX.SUBSTITUTE(A1," \d{7} "," xxxxxxx ")

http://download.cnet.com/Morefunc/30...-10423159.html

eskimo,

The add-in martindwilson suggested should be able to accomodate what you're looking for. If you want to stick with the macro, I made a slight change to compensate for the comma issue. As for the 255 character limit, I'm not sure what's causing that

Please Login or Register  to view this content.