How to Use MID with Decimal Zeros in Excel
The answer depends on whether the cell contains the text 315.00 or the number 315 formatted to show two decimals. The MID function returns characters from text. It can read the two zeros only when those zeros exist as text, or after you convert the displayed number to text with TEXT.
To check the input type, use =ISTEXT(A1) or =ISNUMBER(A1). To enter the test value as text, type '315.00; the leading apostrophe is an input marker and is not part of the cell's text.
If A1 is text such as 315.00
Use the decimal point as the position marker:
=MID(A1,FIND(".",A1)+1,1)For 315.00, the decimal point is character 4. The formula starts at character 5 and returns the first decimal digit, so the result is the text value 0. The second zero is:
=MID(A1,FIND(".",A1)+2,1)A returned 0 is not the same as an empty string. Microsoft’s MID documentation says that if the start position is greater than the text length, MID returns empty text. You can make that visible:
=IF(MID(A1,FIND(".",A1)+3,1)="","missing digit",MID(A1,FIND(".",A1)+3,1))With 315.00, the third decimal digit is missing, so this returns missing digit. The first and second decimal digits return actual 0 characters.
To return both decimal digits together, use =MID(A1,FIND(".",A1)+1,2). It returns the text 00 for 315.00. If the source has no decimal point, FIND returns #VALUE!; decide whether your input should be treated as a whole number or corrected rather than assuming it contains two zeros.
If A1 is a number displayed as 315.00
Cell formatting can show two decimals even when the stored number is 315. Convert the number to text first:
=MID(TEXT(A1,"0.00"),FIND(".",TEXT(A1,"0.00"))+1,1)For Microsoft 365 or Excel 2021+, LET avoids repeating the same TEXT expression:
=LET(t,TEXT(A1,"0.00"),MID(t,FIND(".",t)+1,1))The older-compatible version above is the same idea without LET.
Negative amounts and yuan/jiao/fen extraction
To split an amount into yuan, jiao, and fen before writing the amount in words, use the absolute value for digit extraction and keep the sign separately. Microsoft’s ROUND function rounds a number to a specified number of digits; here the amount is rounded to whole fen by multiplying by 100 first.
| Need | Formula | Example for -315.05 |
|---|---|---|
| Sign | =IF(AND(A1<0,ROUND(ABS(A1)*100,0)>0),"-","") | - |
| Total fen | =ROUND(ABS(A1)*100,0) | 31505 |
| Yuan | =QUOTIENT(ROUND(ABS(A1)*100,0),100) | 315 |
| Jiao | =QUOTIENT(MOD(ROUND(ABS(A1)*100,0),100),10) | 0 |
| Fen | =MOD(ROUND(ABS(A1)*100,0),10) | 5 |
These arithmetic formulas assume a numeric amount in A1. For -0.004, the rounded amount is zero and the sign formula leaves the sign blank. Digit extraction alone does not produce Chinese financial uppercase wording; it supplies the yuan, jiao and fen values for that separate conversion step.
The key distinction is again visible: the jiao value is the number 0, not a missing character. If you use MID on a text value shorter than expected, you may get an empty string instead.
Locale notes
- The examples use a dot as the decimal point because the original example is
315.00. If your source text uses315,00, search for a comma:FIND(",",A1). - In some Excel locales, function arguments use semicolons instead of commas, for example
=MID(A1;FIND(".";A1)+1;1). - TEXT format codes can also be displayed differently by locale. If
"0.00"does not produce the expected decimal text in your Excel UI, use the local equivalent format string.
So, to reference the zero after the decimal point, use MID directly only when the zero is part of the text. For numeric amounts, create a two-decimal text version first or use rounded-cent arithmetic when the next step is currency wording.