Master LAMBDA UDFs: Auto-Increment Text Codes & INR Words Converter | DAXified

🚀 Master LAMBDA UDFs: Auto-Increment Codes & INR Words

Build Production-Ready Excel Functions for Audit & Accounting Workflows

Introduction: Why These LAMBDA Functions Are Game-Changers

If you’ve ever struggled with manually incrementing audit codes like AA to AB, or converting amounts like 145678.50 into “Rupees One Lakh Forty Five Thousand Six Hundred Seventy Eight and Fifty Paise Only” — you know the pain of repetitive Excel work.

In this comprehensive guide, I’ll walk you through two powerful LAMBDA User-Defined Functions (UDFs) that I’ve battle-tested in production audit environments with thousands of daily users:

📊 TEXT_INCREMENT Function

Automatically increments alphanumeric codes (A→B, Z→AA, AZ→BA) with case control

💰 INR_WORDS Function

Converts numbers to Indian Rupees words format with perfect compliance

These aren’t just formulas — they’re production-grade tools that handle edge cases, support dynamic parameters, and scale across enterprise audit operations.

📊 Function #1: TEXT_INCREMENT – Smart Alphanumeric Incrementer

What Does It Do?

The TEXT_INCREMENT function takes your last audit code, invoice number, or any alphanumeric sequence and intelligently generates the next one. It works just like Excel columns: A, B, C… Z, AA, AB…

Key Features:

🔄 Base-26 Logic

Mimics Excel’s column naming system perfectly

🎨 Case Control

UPPER, LOWER, or PROPER case output

🛡️ Error Handling

Handles blank ranges and invalid inputs gracefully

📋 Range Support

Reads from any range to find the last non-blank value

The Complete Formula:

=LAMBDA(seed_range,[case_type],
    LET(
        C, IF(ISOMITTED(case_type), "UPPER", UPPER(case_type)),
        last, IFERROR(LOOKUP(2, 1 / (TOCOL(seed_range & "", 1) <> ""), TOCOL(seed_range & "", 1)), ""),
        S, UPPER(TRIM(last)),
        L, LEN(S),
        N0, IF(L = 0, 0, SUM((CODE(MID(S, SEQUENCE(L), 1)) - 64) * 26 ^ (L - SEQUENCE(L)))),
        N, N0 + 1,
        enc, LAMBDA(f,n, IF(n <= 26, CHAR(64 + n), f(f, QUOTIENT(n - 1, 26)) & CHAR(65 + MOD(n - 1, 26)))),
        T, enc(enc, N),
        SWITCH(C, "UPPER", T, "LOWER", LOWER(T), "PROPER", PROPER(T), PROPER(T))
    )
)

How the Logic Works:

Step 1: Find Last Value - Uses LOOKUP with TOCOL to extract the last non-empty cell from any range.

Step 2: Convert to Number - Treats each character as a base-26 digit. "A" = 1, "Z" = 26, "AA" = 27.

Step 3: Increment - Adds 1 to the numeric representation.

Step 4: Encode Back - Uses a recursive LAMBDA helper function to convert the number back to letters.

Step 5: Apply Case - Formats output based on the optional case_type parameter.

Example Outputs:

Input Range (Last Value)	Case Type	Output
A1:A10 containing "Z"	UPPER	AA
B2:B50 containing "AZ"	LOWER	ba
C5:C100 containing "ZZ"	PROPER	Aaa
D1:D5 (empty)	UPPER	A
E2:E20 containing "ABCD"	UPPER	ABCE

💰 Function #2: INR_WORDS - Indian Rupees Word Converter

What Does It Do?

Converts any numeric amount to proper Indian Rupees words format - perfect for cheques, invoices, payment vouchers, and audit reports. Handles crores, lakhs, thousands, and paise with complete accuracy.

Key Features:

🇮🇳 Indian Format

Crore-Lakh-Thousand system, not Million-Billion

💯 Paise Support

Handles decimal values correctly with paise

➖ Negative Handling

Adds "Minus" prefix for negative amounts

📝 Proper Case

Professional formatting for documents

Helper Function #1: INR_WORDS_0_99

This helper function converts numbers from 0 to 99 into words. It's used for both the paise conversion and as a building block for larger numbers.

=LAMBDA(n,
    LET(
        m, INT(n),
        ONES, {"Zero","One","Two","Three","Four","Five","Six","Seven","Eight","Nine",
               "Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen",
               "Seventeen","Eighteen","Nineteen"},
        TENS, {"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"},
        IF(m < 20,
            INDEX(ONES, m + 1),
            LET(
                t, QUOTIENT(m, 10),
                o, MOD(m, 10),
                INDEX(TENS, t + 1) & IF(o > 0, "-" & INDEX(ONES, o + 1), "")
            )
        )
    )
)

Helper Function #2: INR_WORDS_INT

This is the main recursive function that handles the rupees portion. It breaks down large numbers into Indian number system components (Crore, Lakh, Thousand, Hundred).

=LAMBDA(n,
    LET(
        m, INT(n),
        IF(m < 100,
            INR_WORDS_0_99(m),
            IF(m < 1000,
                LET(
                    h, QUOTIENT(m, 100),
                    r, MOD(m, 100),
                    INR_WORDS_0_99(h) & " Hundred" & IF(r > 0, " " & INR_WORDS_INT(r), "")
                ),
                IF(m < 100000,
                    LET(
                        th, QUOTIENT(m, 1000),
                        r, MOD(m, 1000),
                        INR_WORDS_INT(th) & " Thousand" & IF(r > 0, " " & INR_WORDS_INT(r), "")
                    ),
                    IF(m < 10000000,
                        LET(
                            lk, QUOTIENT(m, 100000),
                            r, MOD(m, 100000),
                            INR_WORDS_INT(lk) & " Lakh" & IF(r > 0, " " & INR_WORDS_INT(r), "")
                        ),
                        LET(
                            cr, QUOTIENT(m, 10000000),
                            r, MOD(m, 10000000),
                            INR_WORDS_INT(cr) & " Crore" & IF(r > 0, " " & INR_WORDS_INT(r), "")
                        )
                    )
                )
            )
        )
    )
)

Main Function: INR_WORDS

This is the master function that you'll actually use. It combines the helper functions and adds proper formatting with "Rupees" and "Paise".

=LAMBDA(value,
    LET(
        v, value,
        IF(NOT(ISNUMBER(v)), "Rupees Zero Only",
            LET(
                sgn, IF(v < 0, "Minus ", ""),
                x, ROUND(ABS(v), 2),
                rs, INT(x),
                ps, ROUND(MOD(x, 1) * 100, 0),
                rsWords, INR_WORDS_INT(rs),
                psWords, INR_WORDS_0_99(ps),
                core, IF(ps > 0, "Rupees " & sgn & rsWords & " and " & psWords & " Paise Only", 
                         "Rupees " & sgn & rsWords & " Only"),
                PROPER(core)
            )
        )
    )
)

⚠️ Important Setup Order: You MUST create the helper functions first in Name Manager before creating INR_WORDS. Create them in this exact order: 1️⃣ INR_WORDS_0_99, 2️⃣ INR_WORDS_INT, 3️⃣ INR_WORDS. This is because INR_WORDS_INT calls INR_WORDS_0_99, and INR_WORDS calls both helpers.

How It Works:

Validation: First checks if input is a number, returns "Rupees Zero Only" for non-numeric values.

Sign Handling: Detects negative numbers and stores "Minus " prefix.

Split Amount: Separates rupees (integer part) and paise (decimal * 100).

Word Conversion: Calls helper functions to convert each part to words.

Assembly: Combines everything with proper grammar and formatting.

Example Outputs:

Input Value	Output
145678.50	Rupees One Lakh Forty Five Thousand Six Hundred Seventy Eight and Fifty Paise Only
1250000	Rupees Twelve Lakh Fifty Thousand Only
-5000.75	Rupees Minus Five Thousand and Seventy Five Paise Only
0.99	Rupees Zero and Ninety Nine Paise Only
25000000	Rupees Two Crore Fifty Lakh Only

💾 How to Save These Functions in Name Manager

LAMBDA functions need to be saved as Named Ranges before you can use them like regular Excel functions. Here's the complete step-by-step process:

Saving TEXT_INCREMENT Function:

Press Ctrl + F3 to open Name Manager (or go to Formulas > Name Manager)
Click "New" button
In the "Name" field, type: TEXT_INCREMENT
In the "Refers to" field, paste the complete LAMBDA formula shown above
Make sure "Scope" is set to "Workbook"
Click "OK"
Click "Close" to exit Name Manager

Saving INR_WORDS Functions (All Three Required):

Step 1: Create INR_WORDS_0_99 First
- Press Ctrl + F3 to open Name Manager
- Click "New"
- Name: INR_WORDS_0_99
- Paste the INR_WORDS_0_99 LAMBDA formula (see above)
- Scope: "Workbook"
- Click "OK"
Step 2: Create INR_WORDS_INT Second
- Click "New" again
- Name: INR_WORDS_INT
- Paste the INR_WORDS_INT LAMBDA formula (see above)
- Scope: "Workbook"
- Click "OK"
Step 3: Create INR_WORDS Last
- Click "New" one more time
- Name: INR_WORDS
- Paste the INR_WORDS LAMBDA formula (see above)
- Scope: "Workbook"
- Click "OK" and "Close"
Why This Order Matters: INR_WORDS_INT needs INR_WORDS_0_99 to exist first (it calls it), and INR_WORDS needs both helpers to exist. Creating them in wrong order will cause #NAME? errors.

💡 Pro Tip: Always give your LAMBDA functions descriptive names in UPPERCASE. This makes them easy to identify and prevents conflicts with Excel's built-in functions.

🎯 How to Use These Functions (With Parameters)

Using TEXT_INCREMENT:

Once saved in Name Manager, you can call it just like any Excel function:

Basic Syntax:
=TEXT_INCREMENT(seed_range, [case_type])

Parameters:
- seed_range: Required. The range containing your sequence (e.g., A1:A100)
- case_type: Optional. "UPPER", "LOWER", or "PROPER" (default: "UPPER")

Real-World Examples:

// Generate next audit code from column A
=TEXT_INCREMENT(A:A)
Output: If last value is "AZ", returns "BA"

// Generate lowercase code
=TEXT_INCREMENT(B2:B50, "LOWER")
Output: If last value is "z", returns "aa"

// Generate proper case for invoice numbers
=TEXT_INCREMENT(InvoiceRange, "PROPER")
Output: If last value is "Zz", returns "Aaa"

// Start new sequence (empty range)
=TEXT_INCREMENT(D1:D10)
Output: Returns "A" (starts from beginning)

Using INR_WORDS:

Converting numbers to words is now as simple as:

Basic Syntax:
=INR_WORDS(value)

Parameter:
- value: Required. Numeric value or cell reference

Practical Applications:

// Convert cell value to words
=INR_WORDS(E5)
If E5 = 25000, returns: "Rupees Twenty Five Thousand Only"

// Convert formula result to words
=INR_WORDS(SUM(A1:A10))
If sum = 145678.50, returns: "Rupees One Lakh Forty Five Thousand..."

// Handle negative amounts
=INR_WORDS(F10)
If F10 = -5000, returns: "Rupees Minus Five Thousand Only"

// Direct number conversion
=INR_WORDS(1250000.75)
Returns: "Rupees Twelve Lakh Fifty Thousand and Seventy Five Paise Only"

🎓 Learning Tip: Test these functions with edge cases like 0, 0.99, -1000, and very large numbers to understand their behavior fully.

💡 Pro Tip: Avoid Circular References with DROP Function

Here's a genius technique to use TEXT_INCREMENT (let's call it AlphaNext in Name Manager) in the same column where you're building your sequence - without getting circular reference errors!

The Problem:

If you're in cell E17 and write =AlphaNext(E:E), Excel throws a circular reference error because the formula is referencing its own cell.

The Solution: Use DROP to Exclude Current Row

// If you're in cell E17 and want to auto-increment from E16
=AlphaNext(DROP(E$16:E17, -1))

// Generic pattern for any cell:
=AlphaNext(DROP(E$16:E[CurrentRow], -1))

How It Works:

📍 Absolute Start Reference

E$16 - The $ locks the starting row

🔄 Relative End Reference

E17 - Current cell (no $ so it adjusts when copied down)

✂️ DROP Function Magic

DROP(..., -1) - Removes the last row (your current cell)

🛡️ Auto Blank Handling

TEXT_INCREMENT already handles blanks internally with LOOKUP

Step-by-Step Example:

Cell	Formula	What DROP Sees	Result
E16	`=AlphaNext(DROP(E$16:E16, -1))`	Empty (DROP removes E16)	A
E17	`=AlphaNext(DROP(E$16:E17, -1))`	E16:E16 (contains "A")	B
E18	`=AlphaNext(DROP(E$16:E18, -1))`	E16:E17 (contains "A", "B")	C
E19	`=AlphaNext(DROP(E$16:E19, -1))`	E16:E18 (ignores E19)	D

Handling Blank Cells in Sequence:

Scenario: What if E17 is blank?

Cell	Formula	Value
E16	`=AlphaNext(DROP(E$16:E16, -1))`	A
E17	(blank - user skipped)
E18	`=AlphaNext(DROP(E$16:E18, -1))`	B
E19	`=AlphaNext(DROP(E$16:E19, -1))`	C

✅ The LOOKUP inside TEXT_INCREMENT automatically finds "A" as the last non-blank value and increments to "B" - blanks are automatically ignored!

Copy-Down Formula:

Here's the beauty: you can write this formula ONCE in E16 and copy it down 1000 rows. The relative reference E17 automatically adjusts to E18, E19, E20... while E$16 stays locked as your starting point.

// Write this in E16:
=AlphaNext(DROP(E$16:E16, -1))

// Copy down to E17, E18, E19...
// Excel automatically adjusts to:
// E17: =AlphaNext(DROP(E$16:E17, -1))
// E18: =AlphaNext(DROP(E$16:E18, -1))
// E19: =AlphaNext(DROP(E$16:E19, -1))
// ...and so on!

🔥 Power User Tip: This technique works for ANY accumulative calculation where you need to reference "everything above me except me". Use it for running totals, cascading lookups, or dynamic sequence generation. The DROP(..., -1) pattern is your circular reference killer!

Why This Matters for Audit Workflows:

In large-scale audit systems serving thousands of users, this technique enables:

Dynamic File Numbering: Users can add audit files anywhere in the sequence, and codes auto-adjust
Gap Handling: If someone deletes row 50, row 51's code automatically recalculates based on row 49
Template Scalability: One formula can handle 10 rows or 10,000 rows without modification
User Flexibility: Auditors can leave gaps for sub-categories, and the sequence continues correctly

Alternative Naming Convention:

Instead of TEXT_INCREMENT, many users save this function as AlphaNext or NextCode in Name Manager for shorter, more intuitive formulas.

🏢 Real-World Use Cases from Professional Audits

TEXT_INCREMENT Applications:

Audit File Numbering

Auto-generate sequential audit file numbers: AUD-A, AUD-B, AUD-AA...

Branch Code Generation

Create unique branch identifiers across 2000+ locations

Document Tracking

Sequential tracking codes for compliance documents

Test Scenario IDs

Auto-number test cases in audit sampling

INR_WORDS Applications:

Payment Vouchers

Convert payment amounts to words for authorization

Cheque Printing

Automated amount-in-words for cheque generation

Audit Reports

Professional formatting of financial discrepancies

Invoice Generation

Regulatory compliance for invoice amounts

Combined Workflow Example:

Scenario: Auto-generate audit findings with unique IDs and amount descriptions

Column A: Finding ID = TEXT_INCREMENT($A$1:A1)
Column B: Amount = [User Entry]
Column C: Amount in Words = INR_WORDS(B2)

Finding ID (Column A)	Amount (Column B)	Amount in Words (Column C)
A	50000	Rupees Fifty Thousand Only
B	125000.50	Rupees One Lakh Twenty Five Thousand and Fifty Paise Only
C	-2500	Rupees Minus Two Thousand Five Hundred Only

🔧 Common Issues & Solutions

TEXT_INCREMENT Troubleshooting:

Issue: Returns #NAME? error

Solution: The function wasn't saved in Name Manager correctly. Delete and recreate it, ensuring no typos in the formula.

Issue: Returns "A" even though range has values

Solution: Your range might contain numbers or special characters. This function only works with pure alphabetic text.

Issue: Case parameter not working

Solution: Make sure you're typing exactly "UPPER", "LOWER", or "PROPER" in quotes. It's case-sensitive!

INR_WORDS Troubleshooting:

Issue: Returns #NAME? error

Solution: Missing helper functions. You must create all three functions: INR_WORDS_0_99, INR_WORDS_INT, and INR_WORDS in that exact order.

Issue: Shows "Rupees Zero Only" for valid numbers

Solution: The cell might contain text that looks like a number. Use VALUE() to convert it first.

Issue: Paise calculation slightly off

Solution: This is due to Excel's floating-point precision. The formula uses ROUND() to handle this, but check your source data format.

Issue: INR_WORDS_INT shows #NAME? even after creating it

Solution: You created INR_WORDS_INT before INR_WORDS_0_99. Delete INR_WORDS_INT and recreate it after ensuring INR_WORDS_0_99 exists.

Issue: Function works but shows weird spacing or formatting

Solution: Check your LAMBDA formula for extra spaces in the string concatenations. The formula should have single spaces in expressions like " Hundred" and " and ".

⚡ Advanced Tips & Performance Optimization

For TEXT_INCREMENT:

Use dynamic named ranges instead of entire columns for better performance. Instead of =TEXT_INCREMENT(A:A), use =TEXT_INCREMENT(A1:A1000) where 1000 is your expected max rows.

For INR_WORDS:

If you're converting thousands of amounts, consider using this formula only in a summary/print area. Calculate amounts normally, then apply INR_WORDS only where needed for output.

Creating a Library:

Save both functions in a template workbook named "LAMBDA_Library.xlsx" that you keep in your XLSTART folder. This makes them available in all new workbooks automatically.

Extending TEXT_INCREMENT:

You can modify this function to:

Add prefix/suffix (e.g., "AUD-A", "INV-AA")
Support numeric suffixes (e.g., "A1", "A2")
Skip certain letters (e.g., skip "I" and "O" to avoid confusion)

🎓 Your Next Steps

You've just learned two powerful LAMBDA functions that can transform your audit and accounting workflows. These aren't just theoretical exercises — they're production-tested tools handling millions of transactions in professional environments.

What to do next:

Save both functions in your Name Manager right now
Test them with your actual work data
Explore the complete library of 22 production-ready LAMBDA functions
Join the DAXified community to share your own creations

Remember: Excel's LAMBDA functions are not just about automation — they're about building reusable, shareable, and scalable solutions that elevate your entire team's productivity.

For complete formulas including the INR helper functions, detailed breakdowns, and 20+ more advanced LAMBDA functions covering audit sampling, reconciliation, and data validation, visit the comprehensive guide:

📚 Master Excel LAMBDA Functions - Complete Guide

🚀 Ready to Master Excel Automation?

Join thousands of audit professionals transforming their workflows with advanced Excel techniques

🎥 Subscribe on YouTube 💬 Join WhatsApp Community 📖 Read Full LAMBDA Guide

About DAXified

Professional Excel & Data Analysis Education for Internal Audit

Website: www.daxified.co.in

🚀 Master LAMBDA UDFs: Auto-Increment Codes & INR Words

Introduction: Why These LAMBDA Functions Are Game-Changers

📊 TEXT_INCREMENT Function

💰 INR_WORDS Function

📊 Function #1: TEXT_INCREMENT – Smart Alphanumeric Incrementer

What Does It Do?

Key Features:

🔄 Base-26 Logic

🎨 Case Control

🛡️ Error Handling

📋 Range Support

The Complete Formula:

How the Logic Works:

Example Outputs:

💰 Function #2: INR_WORDS - Indian Rupees Word Converter

What Does It Do?

Key Features:

🇮🇳 Indian Format

💯 Paise Support

➖ Negative Handling

📝 Proper Case

Helper Function #1: INR_WORDS_0_99

Helper Function #2: INR_WORDS_INT

Main Function: INR_WORDS

How It Works:

Example Outputs:

💾 How to Save These Functions in Name Manager

Saving TEXT_INCREMENT Function:

Saving INR_WORDS Functions (All Three Required):

🎯 How to Use These Functions (With Parameters)

Using TEXT_INCREMENT:

Real-World Examples:

Using INR_WORDS:

Practical Applications:

💡 Pro Tip: Avoid Circular References with DROP Function

The Problem:

The Solution: Use DROP to Exclude Current Row

How It Works:

📍 Absolute Start Reference

🔄 Relative End Reference

✂️ DROP Function Magic

🛡️ Auto Blank Handling

Step-by-Step Example:

Handling Blank Cells in Sequence:

Copy-Down Formula:

Why This Matters for Audit Workflows:

Alternative Naming Convention:

🏢 Real-World Use Cases from Professional Audits

TEXT_INCREMENT Applications:

Audit File Numbering

Branch Code Generation

Document Tracking

Test Scenario IDs

INR_WORDS Applications:

Payment Vouchers

Cheque Printing

Audit Reports

Invoice Generation

Combined Workflow Example:

🔧 Common Issues & Solutions

TEXT_INCREMENT Troubleshooting:

INR_WORDS Troubleshooting:

⚡ Advanced Tips & Performance Optimization

For TEXT_INCREMENT:

For INR_WORDS:

Creating a Library:

Extending TEXT_INCREMENT:

🎓 Your Next Steps

🚀 Ready to Master Excel Automation?

About DAXified

Leave a Comment Cancel Reply