Customized Find/Replace to Update Numbers Using Specified Criteria

[Guest]

I have a frequently-updated report with many cells that contain both text and
numbers. Often, the numbers--and only those falling within a certain
numerical range--need to increment by a set amount, say four or five. Is
there a way to update numbers which fall within a specified numerical range
by a specified amount? I know this isn't clear, so here's an example: Say
that, in Column A, there are 5000 filled cells, each of which contains
various text descriptors with numbers scattered amongst them:

Cell A1: Pig, 100, dog, 512, cat, 999, orangutan, 550
Cell A2: Pig, 102, raging bull, 551, cow, 601
Cell A3: Rabid dog, 201

What I need to do is tell Excel to look through all of the cells and,
whenever it finds numbers between 500 and 600, to increase them by 4, but to
leave everything else alone. The desired result would be:

Cell A1: Pig, 100, dog, 516, cat, 999, orangutan, 554
Cell A2: Pig, 102, raging bull, 555, cow, 601
Cell A3: Rabid dog, 201

I know I can just do a series of find/replace actions, but this report
updates often and the range of numbers that may need to update varies
frequently as well (not to mention the increment change amount, so it's a
whole lot of work. Is there a macro or add-in that will do a find-replace
using criteria that the user will supply, perhaps via a user form? I know a
little VBA, but this is pretty complicated and I have no idea. If text and
numbers were iin separate cells, I'm sure this would be easier, but
unfortunately I have to work with the yucky report that I'm given.

I would really, really appreciate any help on this. I'm in way over my head.

 

[Guest]

Hi,

How many is the maximun kind that you may have in one row? in your example
the maximum is 4 which is in the row A1.

Thanks,

 

[Guest]

Hi--

Thanks for your reply! Unfortunately, the number of numerals in each cell
varies horribly, as does the amount of text. The following extract is all in
just one cell on the actual report:

Acids, 283, 485. See also Acid-base reactions; Bases; acid-base indicators
and, 481, 504–505 lab; acidic anhydrides, 492–494; among top ten industrial
chemicals, 481 table; Brønsted-Lowry definition, 526–527; common names, 182
table; as electrolytes, 888; hyrdronium ions, production of in water, 483,
485; ionization, 486, 488, 499; mono- vs. polyprotic, 585 illus., 486 illus.;
names of common, 182 table; pH scale and, 500, 502–503, 506–508; reaction
with carbonates, 482, 483 illus.; reaction with metals, 236–237, 482, 483
illus.; strong, 498, 498 table; taste and feel of, 480, 519

So I can't actually tell you what the max length of a cell's character
string would be. Which makes this awful thing even more awful, I know. As you
can tell from my example, the report recapulates information in an Index
which is constantly evolving but was set up long ago in this extraordinarily
useless way.

 

[Guest]

Hi,

Wow your actual data is completely different from your first example and i
can not underestand it at all anyways if your data is like below i can do
somthing for you otherwise if your data is like what you send in the second
time i think it is not a computer job and you have to do it manualy coz there
is no rule in your data.

you data should be like:

kind, nymber, kind, number, .....

Thanks,

 

[Guest]

I presented a simplified example to illustrate the problem. Since the issue
involved is to identify specified numerals in cells containing text and
numbers (which I indicated), I wasn't aware that the specific pattern of text
and numerals within a cell's character string would, in this case, be
significant. Certainly, since one can do a find and replace for a numeral
even in the conditions I present (which I've done), I'm surprised that what
you say might be true. Since I can record a simple (though inflexible) macro
to find and replace individual numbers with specified subsitutes, it really
does seem to me that this is, in fact, a "computer problem": An issue of
somehow tying a user form to Excel's existing replace functions.

But perhaps I'm wrong. Who knows. Anyway, thanks for your time.