Forum Discussion
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 =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
- 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)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 ...
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 NextRiny_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.
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.
