Replace numeric values in a cell range by ascending order

georgemathew46

New Member
Joined
Jun 5, 2013
Messages
22
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.. :)
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
511
Office Version
365
Platform
Windows
Hi

If a helper column is OK:
Code:
[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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,629
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

New Member
Joined
Jun 5, 2013
Messages
22
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,036
Office Version
2010
Platform
Windows
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

New Member
Joined
Jun 5, 2013
Messages
22
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,036
Office Version
2010
Platform
Windows
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...

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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,287
Messages
5,485,899
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top