Jul 21 2020 07:40 AM
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!!!!
Jul 21 2020 08:18 AM
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:
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.
Jul 21 2020 08:23 AM
@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
Jul 21 2020 08:29 AM
Jul 21 2020 08:35 AM
@NikolinoDE ??
Jul 21 2020 08:42 AM
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)
Jul 21 2020 08:43 AM
Jul 21 2020 08:59 AM
@NikolinoDE Don't really know what your intentions are, but you are certainly not responding to the issue in this thread.
Jul 21 2020 09:15 AM
Perhaps English<->German translation is very approximate
Jul 21 2020 10:51 AM
Jul 21 2020 11:05 AM
Nope, that's about mapping of clothes sizes (S, M, L, XL,...) to sequential numbers.
Jul 21 2020 11:20 AM
Jul 21 2020 11:22 AM
No problems, have a nice day too
Jul 21 2020 03:45 PM
@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.
Jul 21 2020 03:53 PM
@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.
Jul 21 2020 04:30 PM
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.