Create a new workbook.
Put the values 000 to 900 in column A.
If those values are brought in as 0, 1, ..., 900 in the .CSV file, and you give
them a custom format of 000, then do the same thing in this column.

Put the strings that do the replacements in column B.

(Yep, you'll have a macro that just does the edit|replace 900 times.)

Then put this code into a general module of that same workbook.

Option Explicit
Sub DoLotsOfChanges()

Dim wks As Worksheet
Dim tableWks As Worksheet
Dim myCell As Range
Dim myRng As Range

Set wks = ActiveSheet
Set tableWks = ThisWorkbook.Worksheets("sheet1")

With tableWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With


For Each myCell In myRng.Cells
wks.Range("a1,d1,f1").EntireColumn.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlWhole, searchorder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub

Adjust this line:
wks.Range("a1,d1,f1")
to match the columns that need the "fixing".

Now save this workbook as a nice name.

when you get the next .csv file...
Open the workbook with the code and table.
then open the .csv file.

Make sure you're on the worksheet that needs fixing.

Tools|Macro|macros|
click on DoLotsOfChanges
and click run.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



J. Gutierrez wrote:

OK, let me briefly explain...I work in a medical research lab, and part of my
job is organizing excel files containing patient medical info. The data is
imported as a .csv file from a large cancer registry. There are columns of
data where a three digit number represents text. (example: 000 is heart, 001
is lung, etc.) In order to be able to work with the data, I have to replace
the numbers with the corresponding text. (The values all come out of a
standardized reference) Currently, I have to do this manually, using the Find
and Replace window in excel. With over 900 values to replace, it gets
tedious, especially when I have to repeat the whole process for every project
that we start.

There must be a way to automatically make all of the replacements in the
spreadsheet.....any suggestions?

Thanks in advance for any help.

JG

--

Dave Peterson