excel function replace all letters in document with numbers automatically

Copper Contributor

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 =4  and so on.

 

can anyone help me with a function? Ideally, it would be nice to have a macro or a document wide function so I don't have to put the function in each cell.  thank you so much for the help!!!!

15 Replies

@bradpeterson 

 

It would be fairly easy to write a formula along the lines below:

=IFS(A1="S",1,A1="M",2,A1="L",3....)

and place such a formula in a cell adjacent to the column with your letter size.

Then you could use Format....Column...Hide to hide the letter-size column, and use only the number.

 

This would work best if your data are in an official Excel Table. Which leads me to a question or two:

  • Where is the data in question coming from?
  • How frequently does it change (get updated)?
  • What's the process of which this is a part?
  • What happens "downstream" with the data, especially this size data?

These are not incidental questions. The truly best way to handle your situation may depend a lot on the bigger picture of which it's a part.

@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

 

With VBA

Sub Row_Column_width()
With Sheets("69")
.Rows("50:50").RowHeight = 63.75
.Columns("A:A").ColumnWidth = 4
.Columns("B:H").ColumnWidth = 1.86
.Columns("I:I").ColumnWidth = 35.14
.Columns("J:J").ColumnWidth = 14.71
.Columns("K:K").ColumnWidth = 13
.Columns("L:L").ColumnWidth = 12.86
.Columns("M:M").ColumnWidth = 30
End With
End Sub

If you liked my solution, I would be happy to receive a reply with a cross as the correct answer and a thumbs up. If it is not an option, please ignore or inform.

Nikolino
I know I don't know anything (Socrates)

@NikolinoDE 

Adjust column width according to cell value

 

With file where you can set the column width as desired .... with VBA.
Freehand fished from the Internet and prepared ... completely fresh.

 

... and as I said, if you liked it ... high :-))

 

Nikolino

I know I don't know anything (Socrates)

Here :)

hope I did nothing wrong ...

@NikolinoDE Don't really know what your intentions are, but you are certainly not responding to the issue in this thread.

 

@Riny_van_Eekelen 

Perhaps English<->German translation is very approximate

Hi Riny_van_Eekelen,
Are you sure about that?
As I understood it from the translation (I speak mainly German that does not mean that I can’t speak some other languages), you want to have columns in different widths with VBA.
That's exactly what I did, and at the same time I also showed an additional option.
Therefore I do not understand your suggestion.
Just try to help as far as I can, despite my ignorance ... because the principle is "help to be helped", so the helped can learn something new and who helped to gain some experience.

But as I said, do not want step anyone too close
and if I have disturbed anyone with my comments, please forgive me
... because there was certainly no intention.

Nikolino
Do not look for mistakes, look for solutions.
Henry Ford

@NikolinoDE 

Nope, that's about mapping of clothes sizes (S, M, L, XL,...) to sequential numbers.

Hi Mr. Sergei Baklan & Mr. Riny_van_Eekelen,

Now that Mr. Baklan has brought this to my attention, I have seen the mistake of mine.
I would like to apologize for my mistake to everyone involved ... who can read and googling has an advantage ... what I missed :-(.

Thanks again for the tip and sorry if I accidentally stepped on the wrong foot, it was definitely not my intention ... as I said "help to be helped".

Have a nice day and thank you for your attention :-).

Nikolino
When i know that i don't know, when does i know when i know something?


@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.