• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to replace list of values in Excel

S

siva seethiraju

New Member
Hi,

I have a spreadsheet with 153 columns. Most of these columns has free form text. As it has some sensitive information, I need to change this with some label. For example, if I find ABCD, replace it with XYZD. I have around 300 patterns (like ABCD) to find and replace (i.e with XYZD etc..). Could you please help me on how to achieve it with Excel. Your prompt response will be highly appreciated as I have to finish it by Thursday EOD.

Thanks,
Siva
 
Hi Siva,
I will not suggest you'd be doing CTRL + H about 300 times.
Making formulas also sounds tedious and cumbersome.
A macro seems more appropriate.
Perhaps this one could work.
EDIT: Vletm was just a bit faster, but his macros are way better.
 

Attachments

  • Case 37769_.xlsm
    55.2 KB · Views: 3
GraH - Guido said:
Hi Siva,
I will not suggest you'd be doing CTRL + H about 300 times.
Making formulas also sounds tedious and cumbersome.
A macro seems more appropriate.
Perhaps this one could work.
EDIT: Vletm was just a bit faster, but his macros are way better.
Click to expand...
Thank you for your quick response. Can you also please explain me how to use your spreadsheet with one example that helps me.

Thanks,
Siva
 
To add to my question. I have text like below (I am presenting here small text)
2017-09-28 11:34:07 AM - FirstName LastName (Additional Comments)
SEFGE1 rippled out to production. I ran JCLPLUS there and checked to make sure that everything looked perfect.

I am expecting it to be
2017-09-28 11:34:07 AM - NAME (Additional Comments)
USERID rippled out to production. I ran JCLPLUS there and checked to make sure that everything looked perfect.

Thanks,
Siva
 
You could past your data in the second sheet "ToReplace".

In the sheet values, but your old values in column A and the replacement values in column B. (Like in solution by vletm).
Check via the name manager if rFndRpl refers to the full data range. Replace 31 by your number of rows.

Uploaded a new workbook with titles on first sheet and a button you can press to run the macro.
 

Attachments

  • Case 37769_.xlsm
    54.2 KB · Views: 2
Hi Guido, thanks once again. I didn't understand below statement. May I know where do I need to do.
Check via the name manager if rFndRpl refers to the full data range. Replace 31 by your number of rows.

Thanks in advance,
Siva
 
siva seethiraju - - how to use it:
1) open siva.xlsb -file
2) select Sheet1 -sheet
3) as written in cell E1: Copy in this sheet Your information
4) select Patterns -sheet
5) as written in cell E1: Write Your ~300 texts below 'now' and 'new' texts
Below 'now' text which You would like to change and
below 'new' text which You would like to get after change eg from AA to BB
and text size matters!
6) Press [Do It]-button.
7) Wait ...
 
GraH - Guido
It's better to write that too ...
if not mentioned then someone would think something.
Never knows how many rows there would be!
Sometimes ten and sometimes >100000
... so it would take short or longer time.
 
vletm said:
siva seethiraju - - how to use it:
1) open siva.xlsb -file
2) select Sheet1 -sheet
3) as written in cell E1: Copy in this sheet Your information
4) select Patterns -sheet
5) as written in cell E1: Write Your ~300 texts below 'now' and 'new' texts
Below 'now' text which You would like to change and
below 'new' text which You would like to get after change eg from AA to BB
and text size matters!
6) Press [Do It]-button.
7) Wait ...
Click to expand...
Thank you. Will try this one also.

Thanks,
Siva
 
You must log in or register to reply here.
Back
Top