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.