Excel replace text.

  • Thread starter Thread starter xcao
  • Start date Start date
X

xcao

I import a table from sql server to Excel occassionaly,

I need to format one column in Excel file.

For example, I need to replace numbers in the column with a text,
with weekly, 1 with daily, 16 with monthly etc, 4 with daily etc,

Everytime I get the new file, I have to do it use replace dialoge bo
on the edit menu to do all the replace,

Are there some easy ways to do this, or at least I can save thos
functions so that later I can use it again, instead of doing replac
for each one manually.



Thank
 
One way:

Public Sub ConvertValues()
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
Select Case rCell.Value
Case 1, 4
rCell.Value = "daily"
Case 8
rCell.Value = "weekly"
Case 16
rCell.Value = "monthly"
Case Else
'do nothing
End Select
Next rCell
End Sub
 
Turn on the macro recorder while you do it manually - then turn off the
recorder when you are done. Then you can run this macro to do it.
 
'Add The Following Function to the Sheet where you load the data to.

Function ReplaceValues(FindVal As Variant, ReplaceWithVal As Variant)

Cells.Replace What:=*FindVal*, Replacement:=*ReplaceWithVal,
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
SearchFormat:=False, ReplaceFormat:=False

End Function

'After You load the data, do a call for each replacement you want made
ex:

Call ReplaceValues("8","Daily")
Call ReplaceValues("1","Weekly"
 
Back
Top