Replacing first 4 digits with ****

[PurpleBase]

Hi,

Very, very new to vba so please bear with me. I am currently working with a sheet that has different amounts of data per cell that contains a mixture of words and numbers (again random amounts). What I'm trying to do is:

1. Find all the numbers in any cell that has 8 digits
2. Replace the first 4 digits of each of those numbers with ****

Like I mentioned, I'm new to vba, the most I've achieved so far is to record a macro to do a couple of things. I started replacing the first 4 digits manually but the list is getting longer and the data per cell is increasing too....... any suggestions please.

[Ron Coderre]

Welcome to the forum.

Can you post some sample data so we'll know what you're working with?

[PurpleBase]

Wow, thanks for the quick response!!

OK, a cell might have some thing like this:

i need to remove numbers 34567 12345678 before my boss 00000 99999999 fires me.

Or it may contain more data like:

yesterday my leg was broken 11111 63598546 22222 69875462 33333 65489546 but due to a quick fix 44556 95462357 i managed to 78546 95462348 walk again today.

I am having change the numbers manually to look like;

i need to remove numbers 34567 ****5678 before my boss 00000 ****9999 fires me.
or
yesterday my leg was broken 11111 ****8546 22222 ****5462 33333 ****9546 but due to a quick fix 44556 ****2357 i managed to 78546 ****2348 walk again today.

I am working with account numbers that need to be passed onto developers so they can fix the faulty code but due to policy they are not allowed to see the full account number.

Thanks

[Ron Coderre]

I don't know what happened to your post. It looked like a coded message.

I pulled this information from it:

I need to remove numbers before my boss fires me.

it may contain data like:
34567 12345678
00000 99999999
11111 63598546
22222 69875462
33333 65489546
44556 95462357
78546 95462348

I am having change the numbers manually to look like;

34567 ****5678
00000 ****9999
11111 ****8546
22222 ****5462
33333 ****9546
44556 ****2357
78546 ****2348

If that is accurate, then:
With A1:A7 containing these values:

Please Login or Register  to view this content.

This formula replaces the 1st four digits of the second set of digits with asterisks:

Please Login or Register  to view this content.

Copy that formula down through B7.

With the above data, the formulas return these values:

Please Login or Register  to view this content.

Copy the Col_B calculated values and past_special.values over the Col_A values.
Then delete the Col_B formulas.

Does that help?

[PurpleBase]

Hi,

Not quite, I have added a sample excel file which might explain it a little better. Column C and D (not that D has anything in it yet) will hold the same kind of data of varied amounts.
Hope that explains it better.

[romperstomper]

Try this - it runs on all data in column C but you can adjust if required.

Please Login or Register  to view this content.

[Marcol]

Please Login or Register  to view this content.

[PurpleBase]

Genius!!!! Thanks romperstomper, works perfectly!!