I am using a software that generates similar outputs in the picture below: <img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/kSZVP.png" alt="enter image description here">
I would like to sort this table according to the numbers after the RetValue.
Is there an easy way to do this with VBA?
I am always a big fan of storing numbers in cells. Especially in Excel number are worth storing as such in order to allow for calculations. So, why don't you put into a cell just the number 36 (for example) with the
.NumberFormat = """RetValue ""00""Y"""
Then you still get to see
RetValue 36Y but in fact only 36 is the
.Value of the cell and you are allowed for calculations.
Still, to sort your above table there is nothing special to be considered. Since all cells start with the same string, all cells will be sorted anyway correctly. The only "problem" will be the
RetValue 4Y since it is not a two-digit number.
Once again, I'd try to format it correctly. If that is not an option then I'd go for any of the solutions provided here to extract the number and then sort it as such. During this process I would (as described above) only store the number in the
cell.Value and wrap the text around it as
.NumberFormat to allow for more calculations in the future (if necessary).
Afterwards, you can sort the table for example with this solution: Sort range without sorting it in a spreadsheet
It gives you the option to sort it as an array in memory before pasting the result to the sheet or to use a UDF (entered as an array formula).
<sub>Note for the future: please don't post pictures but rather post formatted text we can copy for testing.</sub>
I wouldn't go for VBA with this, but instead Split Text Into Multiple Cells and in Filter/Sort I would sort by the newly created "__Y" column.
With data in <strong>A1</strong> through <strong>B4</strong>, in <strong>C1</strong> enter:
=--LEFT(MID(A1,FIND(" ",A1)+1,9999),LEN(MID(A1,FIND(" ",A1)+1,9999))-1)
and then run the Recorded macro:
Sub Macro1() Range("A1:C4").Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C1:C4"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A1:C4") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub
VBA may not be required for this.
You can insert the following formula in column C (assuming RetValue in Column A and Value in Column B and your data starts from 3rd row).
=MID(A3, FIND(" ",A3,1)+1, FIND("Y",A3,FIND(" ",A3,1))-10)
You can sort on column C.