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

[PhaniDasaga]

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]

Format Custom 00#

 

[PhaniDasaga]

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

 

[AlanSidman]

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.

 

[GraH - Guido]

Try 000\-000

 

[bosco_yip]

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.

 

[vletm]

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.