Forum Discussion
excel function replace all letters in document with numbers automatically
bradpeterson I'm far from an expert on VBA, but the code below does the trick. Specify the range (A1 to C100 in the example) that you want to evaluate and add "Case" statements for all relevant size indicators. I trust that a real VBA expert can come up with something better.
Sub ReplaceSize()
Dim ws As Range
Set ws = ThisWorkbook.Sheets("Sheet1").Range("A1:C100")
For Each c In ws
Select Case c
Case "S"
c.Value = 1
Case "M"
c.Value = 2
Case "L"
c.Value = 3
Case "XL"
c.Value = 4
End Select
Next
Riny_van_Eekelen thank you for your solution. I tried the code and found some success but then I tried to upload that roster into my webservice. unfortunately, the function in the cell tripped up my importer. so I have to find a different solution besides excel.
bradpeterson just to poke my two cents in here,
I can see where the VBA code may trip up the importer, but if you use the first suggestion to have an "if" statement then I think that you can make that work.
Before you do this, are you importing data regularly or is this a one time event? Will you have to repeat the 'clean up' each time? If so, you might want to run this as a macro to record the building of the IF statement, the copy of the resulting column and the paste of the results - so you can have your raw data, run the macro and then import the data.Just me thinking out of the box.
thank you for your response. I think you're right about using a macro. that I could run on all my rosters. however, after much trial and error my boss has changed my direction so I'm moving into different solutions.
