I have some data like

ID Comments
12 No-DS/ALG PCN
13 NKMA/NO DS
15 Allergy PCN / HTN
16 All Lasix / NO/DS

I need to standarize many of the non-standard comments. So I am
looking for a way to go thru the comments field and find for example
NO-DS or NO DS or NO/DS or NKDS and replace all of those with NODS and
to also go thru and replace Allergy or ALL or Alg with Allergies. Any
ideas on how to do this? Ideally I would like to try making a table of
the items I wanted replaced and what to replace them with. For example
Find Replace
NO-DS NODS
NO/DS NODS
NKDS NODS
ALL Allergies
then use something like replace or vlookup to fix these, but I am not
sure how to get the formulas to use the table for the find and replace
pairs.

Thanks for any ideas,
Andrew V. Romero

Any takers on this? I still haven't found any methods to make this
work well other than multiple find and replace commands.

Thanks,
Andrew V. Romero

wrote:
I have some data like

ID Comments
12 No-DS/ALG PCN
13 NKMA/NO DS
15 Allergy PCN / HTN
16 All Lasix / NO/DS

I need to standarize many of the non-standard comments. So I am
looking for a way to go thru the comments field and find for example
NO-DS or NO DS or NO/DS or NKDS and replace all of those with NODS and
to also go thru and replace Allergy or ALL or Alg with Allergies. Any
ideas on how to do this? Ideally I would like to try making a table of
the items I wanted replaced and what to replace them with. For example
Find Replace
NO-DS NODS
NO/DS NODS
NKDS NODS
ALL Allergies
then use something like replace or vlookup to fix these, but I am not
sure how to get the formulas to use the table for the find and replace
pairs.

Thanks for any ideas,
Andrew V. Romero

Standard abbreviations would be nice. Unfortunately, the data has
already been entered into an old system and we are converting over to a
new system. It still seems like I should be able to create a table
like

OldValue, NewValue
OldValue1, NewValue1
etc

and create a macro which can use that table to go thru and find the old
values and replace them with the new values. I know you could do this
with a 2d array, I am just not sure how to create it and use it in
excel.

Thanks,
-Andrew V. Romero

JLGWhiz wrote:
Too many variables for an easy fix. One solution to your situation would be
to have the people providing the data to use some kind of standard in their
abbreviations.
If you can do it without causing a problem in your local procedures, sorting
the file using that column as the primary sort key might help in assessing
the remedy.
" wrote:

Any takers on this? I still haven't found any methods to make this
work well other than multiple find and replace commands.

Thanks,
Andrew V. Romero

wrote:
I have some data like

ID Comments
12 No-DS/ALG PCN
13 NKMA/NO DS
15 Allergy PCN / HTN
16 All Lasix / NO/DS

I need to standarize many of the non-standard comments. So I am
looking for a way to go thru the comments field and find for example
NO-DS or NO DS or NO/DS or NKDS and replace all of those with NODS and
to also go thru and replace Allergy or ALL or Alg with Allergies. Any
ideas on how to do this? Ideally I would like to try making a table of
the items I wanted replaced and what to replace them with. For example
Find Replace
NO-DS NODS
NO/DS NODS
NKDS NODS
ALL Allergies
then use something like replace or vlookup to fix these, but I am not
sure how to get the formulas to use the table for the find and replace
pairs.

Thanks for any ideas,
Andrew V. Romero