NUMBERING IN EXCEL - fourdigit

[mandura]

hi folks,

does anyone know a way to solve my numbering problem in excel.

i have column A a numbering like this "25/06", or 1077/06", or 555/12.
i want to have the column ascending and fourdigit, so that it goes like this:
0025/06
1077/06
0555/12

of course there are a few hundered rows.

can u help me please!?

cheers


mandura

[zbor]

Use in B1:

Please Login or Register  to view this content.

[jason.b75]

25/06 could be evaluated as June 25th and evaluate incorrectly with zbor's formula, this change will allow for that.

Please Login or Register  to view this content.

[JimBobBowie]

This formula will build you up a 000/00 formatted text string even is the lead 0 in the second half of the original string is missing.

Please Login or Register  to view this content.

JimBobBowie

[jason.b75]

This formula will build you up a 000/00 formatted text string even is the lead 0 in the second half of the original string is missing.

Please Login or Register  to view this content.

JimBobBowie

With A1 formatted as anything other than text, enter your formula, then type 25/06 into A1 and you will get a #VALUE! error.

[mandura]

guys, you are awesome!!
thank you very, very much!!

i fixed that problem with your help, but now i got another one.
i want to sort the data ascending, but excel does not consider the year, for example /07.

when i sort the column its like this:

0021/05
0023/05
0032/06 !!!!
0088/05

do you guys have a solution for this problem?

thanks again to all of you guys!!

[zbor]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mandura]

Here you go!

thx!

[jason.b75]

gi want to sort the data ascending, but excel does not consider the year, for example /07.

The data is in text format, sorting by the first 4 digits, with the last 2 as a tiebreaker, excel will have no way of knowing that the second part is a year reference.

In C1 enter, =RIGHT(B1,2) and fill down.

Sort by column C, then column B

[zbor]

Add helper column and use:

Please Login or Register  to view this content.

Then sort by that column.

[mandura]

this worked for me!

thank you very much i lijep pozdrav iz lijepe naše

[zbor]

Glad it worked.

Također