Add a second line of the same number but add a "a" to it

Put the "0611" text in A1... then put this formula on A2 and bellow:

Please Login or Register  to view this content.

Don't forget to put the 0611 as TEXT format, or do format with 4 numbers

Assuming the values start as 0611, 0612, ..., 0724, and start in cell A1, IMMEDIATELY BELOW the last number, type =A1&"a". Drag the formula down until you just get an "a", that is, after the number 0724.

Then select all the new formulae, Ctrl-C to copy the data, and Paste Special | Values

Now you can use a Custom Sort on column A and choose Sort Numbers and Text separately.

Add the values and formulas in A2:A4 as shown:

	A	B
1	Header
2	611	A2: Input
3	611a	A3: =A2&"a"
4	612	A4: =A2+1

Select A3:A4 and drag down:

	A
1	Header
2	611
3	611a
4	612
5	612a
6	613
7	613a
8	614
9	614a
10	615
11	615a
12	616
13	616a
14	617
15	617a
16	618
17	618a

You're welcome.


Must admit, I like shg's method if you have a starting number from which you can generate the rest of the numbers ... and they are numbers as opposed to what looked like text codes (with a leading zero)




If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.