Replace numeric values in a cell range by ascending order

[georgemathew46]

Hi Geek,

My requirement is to renumber the values in a cell range.
For example:

If column e has 4 values of different numbers..

Column E:
HT_53
HT_51
HT_07
HT_34

i want them to be renumber by ascending order.
like below output is

HT_01
HT_02
HT_03
HT_04

Could you please help out ...
Many thanks..

 

[shift-del]

Hi

If a helper column is OK:

[COLOR=#222222]=LEFT[/COLOR][COLOR=#0000dd](E1,3)[/COLOR][COLOR=#222222]&TEXT[/COLOR][COLOR=#0000dd](ROWS[/COLOR][COLOR=#222222](F$1:F1)[/COLOR][COLOR=#0000dd],"00")[/COLOR]

 

[mikerickson]

If the prefix "HT_" is always the same and the numeric portion always has two decimals, a normal Sort will do what you want.

 

[georgemathew46]

Thanks a lot for your response geeks ...

@shift_del coùld you please provide the same in macro with added functionality of checking in ranges..

for example.. i want the same formula to apply in a range of cells ie e1 to e10

@MrExcelMvp Great Thanks for your suggestion sir. But my requirement is renumbering not sorting..

 

[Rick Rothstein]

Thanks for your suggestion sir. But my requirement is renumbering not sorting..

Try this... enter HT-01 into the first cell that you want it in (that would be the cell currently holding HT-53), then after you have entered it, select that cell and then hover the cursor over the blank square in the bottom right corner until the cursor turns into a black "plus" sign, then click/drag down for as many cells as you want... when you release the mouse button, you will have HT-01, HT-02, HT-03, etc. in the cells you click/dragged down over.

 

[georgemathew46]

Thanks Rick. But i need the same in macro.. actually the above requirement is a part of a different code... Could you please help me out ?!

 

[Rick Rothstein]

Thanks Rick. But i need the same in macro.. actually the above requirement is a part of a different code... Could you please help me out ?!

That means there is a lot of details that we need to know that you have not told us. That means we can only guess at what to do. Here is my guess, see if you can adapt it to your setup and existing code...

Sub RenumberColumnE()
  Dim CellText As String
  CellText = Range("E1").Value
  Mid(CellText, Len(CellText) - 1, 2) = "01"
  Range("E1").Value = CellText
  Range("E1").AutoFill Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
End Sub

 

[shift-del]

@shift_del coùld you please provide the same in macro with added functionality of checking in ranges..

Sorry, I'm not a VBA-expert.