Cleaning imported data
RIGHT function: extract trailing serial digits with leading zeros
Pull a fixed-width suffix from the end of invoice or voucher IDs while preserving leading zeros as text, and handle short or blank cells cleanly.
Four-digit serial suffixes at the end of invoice codes
Invoice numbers like INV-2026-0042 always end with a four-digit sequence number. Extracting that suffix as text keeps the leading zeros ("0042") intact for audit logs.
| Invoice ID (A) | 4-char suffix |
|---|---|
| INV-2026-0042 | 0042 |
| INV-2026-1980 | 1980 |
| 99 | 99 |
Count characters from the right end
RIGHT(text, num_chars) extracts characters from the end of the string: =RIGHT(A2, 4) returns "0042". Omitting num_chars (=RIGHT(A2)) defaults to 1 character ("2").
=RIGHT(A2,4)Short codes, blank rows, and numeric conversion
When A4 has fewer than four characters ("99"), RIGHT(A4, 4) returns "99" without padding. Wrap potentially empty rows with =IF(A5="", "", RIGHT(A5, 4)), and note that negative lengths such as RIGHT(A2, -1) return #VALUE!.
Because RIGHT returns text, "0042" does not equal numeric 42 until wrapped in VALUE(RIGHT(A2, 4)).
=IF(A5="","",RIGHT(A5,4))Try the example
Use the original values or change them to test your understanding.