Find & Replace data with multiple If range

[miners]

Hi there,

I was wondering if anyone could help me in scripting the code for solution of my problem.
say that i have alist of data in "A2:A", which are a string file (text)
and i want to rename each data to a certain name that defined in
range "B1:B2" (String file too).

For Example: for range
C2 = S16e
C3 = 16/E
C4 = 18W
C5 = Site18 w

& The target name is to be:
B1=16E
B2=18W

For case above, i need to replace C2&C3 in to 16E, while C4&C5 to be 18W.

I really appreciate for your time & help,
Miners

[JeanRage]

Hi,

It looks as if the function SUBSTITUTE() would solve your problem ...

HTH

[jaslake]

Hi miners
You would be better served if you attached a dummy file showing what you have and what you would like it to be (before and after) as your post is somewhat confusing. You refer to

alist of data in "A2:A",

and yet your "sample data"

For Example: for range
C2 = S16e
C3 = 16/E
C4 = 18W
C5 = Site18 w

refers to something entirely different such that I can't readily tell for certain what you're looking for.
I'd be glad to look at the issue with more information. John

[miners]

Thanks for the response jaslake&JeanRage,

you're right there's a mistake in the example given, for the sample data, instead of C2:C5, they should be in range A2:A5.

I have attached the dummy : before the substitution & after the substitution, which match the target name.

The inconsistency in the data are sometime they have lower/uppercase, space, slash, etc. But they would have the correct number & letter (i.e 16, E, 18, W).

Thanks for your time
Miners

[jaslake]

Hi miners
Try the attached. It's been tested on your sample data and appears to work.
Hope this helps. John

[miners]

Hi Thanks jaslake,

that's very cool, it works as i wanted verywell. However it seems the formula working on replacing based on 5 criterias:
space, slash,uppercase,lowercase,"-"
I was wondering if i add another criteria such as:
"AB" on sample data and it'll need to be replaced as "ABC"
what change need to be made in the code?
i guess that also would make the code more flexible to accomodate any new sample data ID in the future(?)

really appreciate your help & time
Rgds,
Miners

[jaslake]

Hi miners
Change this line of code (notice the underlined).

Please Login or Register  to view this content.

As far as I know, you can add as many replaces as you please.
Hope this helps. John

[miners]

Thanks jaslake

Rgds,
Miners

[jaslake]

Glad I could help. If your satisfied, please mark your post as solved.
John