Replace 6-digit text numbers

[jomili]

I have a column populated by text fields, such as "Contract #4521897" or "Mandy Jone", which are descriptive and I want to keep. Also within the column are text numbers such as 015643 or 957351, which are not descriptive. I want to replace only the 6-digit text numbers. How do I do this via vba?

Thanks,
John

[Andrew-R]

What do you want to replace them with? Are the numbers you want to replace always in a cell by themselves? If so you could use a formula, rather than having to mess around with VBA.

[jomili]

Andrew,
Sorry for not explaining further. This is a small routine in a much larger process that I'll be going through every day. It starts out with 12 workbooks, each having a worksheet with between 20K and 60K rows of data, in 68 columns. In ONE particular column in each of these workbooks I want to replace the 6 digit text numbers with something (I haven't determined what that "Something" is yet; maybe just text saying "No usable info here").

Since there's so many workbooks, and so many rows, and I'll be doing this every morning (part of "normalizing data"), I've got the whole operation set to run by macros, but I'm stumped at how to configure the "Replace" piece.

[watersev]

hi, Jomili, may be sort of this would help, run code "test"

[jomili]

Watersev,

It works, but I sure don't understand how. Can you explain it to me?

[jomili]

Update: it works, but not exactly. IF I have 6 characters, such as SixDig, it doesn't replace (which is the behavior I want) but if I have a number such as 456789123, it replaces the first 6 digits (which is a behavior I don't want).

[watersev]

hi, Jomili, another guess as still there is no sample data available, please check attachment

[jomili]

Watersev,

I apologize that I didn't post any sample data; that was thoughtless of me. But I've tried your macro on my sample data and it appears to work flawlessly. My data is a combination of Text strings, and Text and non-text numerical sequences. I simply want to change any string of only six numbers to a common phrase, and it looks like you've solved the "how to" part of it. It would help me if I could understand how you did it. Would you care to explain?

Thanks,
John

[snb]

Based on Watersev's example:

Please Login or Register  to view this content.

[watersev]

Please Login or Register  to view this content.

[jomili]

SNB,
Good try, but no go. I've attached my sample data. This is supposed to be a "Description" of expense. The 6 digits numbers contained in this mess convey no usable information, so I want to take them out (replace with "Whatever"). When I tried your macro, it not only replaced the 6 digit numbers, but the other numbers as well.

[snb]

Even simpler:

Please Login or Register  to view this content.

[jomili]

Almost. I realize I didn't include any in my sample, but frequently these are text numbers. Your last code doesn't work on text numbers.

[jomili]

Watersev,

I missed your post where you stepped through all the steps. Thank you very much for doing that. I'll have to study this.

[snb]

A precise description of a question is 95% of the solution.

Please Login or Register  to view this content.

[jomili]

Hmmm,

This one is pasting #VALUE! in all cells in column C when I run it against my test data.

[snb]

So you forgot to remove the last bracket....

Please Login or Register  to view this content.

[jomili]

Well, I guess I did forget to remove the last bracket. Thanks for straightening that out for me. I've tested your last code and it works fine on both text and "normal" numbers. However, for lines having a different text string ("# 232710 Dome Notary Seal Lo") I'm getting the value error. What can we do about that?

[Leith Ross]

Hello jomili,

Here is VBA solution that works. I have added a button to the sheet to run the macro. Here is the macro code.

Please Login or Register  to view this content.

[snb]

Now we can combine:

Please Login or Register  to view this content.

[jomili]

Leith,
Good job! Got it on the first try!

Both yours and Watersev's solutions make use of the "RegExp" object, and use "Pattern", which I haven't encountered before. Something else I need to study up on.

SNB,
Excellent work! Your last attempt does the trick.

Now my dilemma is which of 3 equally good routines to use. Oh well, that's my problem. Thanks to all 3 of your for the help and showing me how much I still need to learn.

Thanks,
John

[snb]

I think I found the oneliner I was looking for:

Please Login or Register  to view this content.