How to parse decimal digits after dot in Google Sheets

Other Questions Google Sheets
1

I need help with extracting specific digit patterns from decimal values in my spreadsheet. Here’s what I’m working with:

Format: DZN.Digits
Digit range: 0-11

Examples:
0.2 represents 2 items
0.8 represents 8 items  
0.10 represents 10 items
0.11 represents 11 items

When I try using the MOD function, it gives me the fractional portion but I run into issues. The main challenge is telling apart 0.2 (meaning 2 items) from 0.20 (which should mean 20 items if it existed)

What formula or approach can I use in Google Sheets to properly extract these digits from my input values? I’m stuck on this and would really appreciate some guidance!

2

Your problem is that Google Sheets sees 0.2 and 0.20 as the same number - because they are. I ran into this with product codes that had decimals. You can try =VALUE(SUBSTITUTE(A1,LEFT(A1,FIND(".",A1)),"")) to strip everything before and including the decimal, but it won’t fix the real issue. Once you type 0.20, Sheets instantly changes it to 0.2. The only workaround that actually works is entering your values as text by wrapping them in quotes - type “0.20” instead of 0.20. Then use =MID(A1,3,LEN(A1)-2) to grab the digits after the decimal. Bottom line: if you don’t keep the original text format, there’s no way to tell which trailing zeros actually matter.

3

ugh, decimals for non-numeric data r the worst! try =RIGHT(SUBSTITUTE(A1, ".",REPT(" ",100)),100) then trim it. this grabs everything after the dot as text. but like everyone else said, sheets will still mess with your input unless you set text format first. maybe use a different separator like underscore instead?

4

I ran into this exact problem with inventory codes that used decimals. Google Sheets automatically converts your input to numbers instead of keeping the original text format. Here’s what worked for me: if your data’s in column A, try =VALUE(MID(TEXT(A1,"0.00"),3,10)). But that only works if you’ve got a consistent decimal format. Better solution - enter your data as text first (add an apostrophe before typing or format the column as text). Then use =VALUE(MID(A1,3,10)) to grab everything after the dot. This way 0.2 and 0.20 stay different since they’re stored as separate text strings. Honestly though, you might want to ditch the decimal format altogether. Something like “DZN-02” or “DZN-10” would save you this headache.