Forum Discussion

Copper Contributor

Jul 21, 2020

excel function replace all letters in document with numbers automatically

Hello, I'm in need of some help and I cannot find a solution online. I have a document that has uniform sizes. i.e. S, M, L, XL I need to replace those sizes automatically: S=1, M=2, L=3, xl...

excel

Formulas and Functions

Riny_van_Eekelen

Platinum Contributor

Jul 21, 2020

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

bradpeterson

Copper Contributor

Jul 21, 2020

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.