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

#### PhaniDasaga

##### 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.

#### AlanSidman

##### Well-Known Member
Format Custom 00#

#### PhaniDasaga

##### 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

#### Attachments

• 10.1 KB Views: 4

#### AlanSidman

##### 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.

Try 000\-000

#### bosco_yip

##### 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)

Then

2] In B1, formula copied down:

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

=TEXT(SUM(IMREAL(IMDIV(A1&"i",{1,"-i"}))*10^{3,0}),"000\-000")

or

=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:

=TEXT(LEFT(A1,FIND("-",A1)-1),"000-")&TEXT(MID(A1,FIND("-",A1)+1,3),"000")

or

=TEXT(TEXT(,"[\$"&A1&"]"),"000-")&TEXT(MID(A1,FIND("-",A1)+1,3),"000")

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

Regards.

Last edited:

#### vletm

##### Excel Ninja
PhaniDasaga
If You would like to see it in same cell,
then this sample could be a solution for You.
Note: Those cells have to have be formatted as text before use.

#### Attachments

• 15.1 KB Views: 3