• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

when i enter 1-9 then excel say 001-009


New Member
Hi, i am trying to enter an index like topic one from page to topage
topic one 1-9

My question is when i enter 1-9 then it should show 001-009
how do i achieve this with custom format in excel.
phani kumar.


New Member
Thank you, Mr. Alan
a1 cell custom format is 00#-00#
value entered is 6-9
cell shows 045- 175
but my question is when i enter 6-9 then it should show 006-009,
can you help to find the way . phani kumar



Well-Known Member
I don't know how to fix this but I do know what is happening. Excel is thinking that because you are putting in a dash "-" between the two numbers, it is thinking this is a date in the current year. ie. June 9, 2023. You are aware that dates in Excel are just numbers 1 up from January 1, 1900.


Excel Ninja
Try this formula way.

1] All source data must be in Text format. (To avoid data auto change to Date format as per AlanSidman mentioned in #4)


2] In B1, formula copied down:

2.1] By split number+Text format: (Text format as per GraH - Guido mentioned in #5)



=TEXT(SUM(TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",50)),{1,50},50))*10^{3,0}),"000\-000")

Remark: I do not have O365, but I known a new Textsplit function can use and shorten the formula.

2.2] By concatenate to join 2 split numbers:




A bit shorter way to extract left number using Text function secret trick (I saw no one reported of this trick in all forums)


Last edited: