Training By Himanshu

Tag: Excel Custom Function

  • Extract Numbers from Text in Excel Using VBA – Works for Indian & European Formats

    🧾 Scenario:

    At Shree Tech Pvt. Ltd., Priya is a finance executive handling a lot of messy Excel data received from multiple vendors and sales teams across India and Europe.

    One day, she encounters a peculiar problem.
    In the “Remarks” column, instead of clean numbers, she sees entries like:

    • "₹3,499 paid in full"
    • "1.250,50 EUR"
    • "Advance of 7500.00 received"
    • "Amount is Rs. 2,50,000/-"

    She needs to extract only the numeric value from these cells, but Excel’s built-in tools can’t help much.

    That’s when her teammate, Rohit, a skilled MIS guy, steps in with a magic wand—a custom VBA function called getNumber.

    🧙‍♂️ The Magic VBA Function: getNumber

    Here’s the full code Rohit shares:

    vbaCopyEditPublic Function getNumber(fromThis As Range) As Double
    'Extract the number from a cell and return it.
    Dim retVal As String
    Dim ltr As String, i As Integer, european As Boolean
    
    retVal = ""
    getNumber = 0
    european = False
    
    On Error GoTo last
    'Check if the range contains European format number i.e. , for decimal point
    If fromThis.Value Like "*.*,*" Then
        european = True
    End If
    
    For i = 1 To Len(fromThis)
        ltr = Mid(fromThis, i, 1)
        If IsNumeric(ltr) Then
            retVal = retVal & ltr
        ElseIf ltr = "." And (Not european) And Len(retVal) > 0 Then
            retVal = retVal & ltr
        ElseIf ltr = "," And european And Len(retVal) > 0 Then
            retVal = retVal & "."
        End If
    Next i
    getNumber = CDbl(retVal)
last:
End Function

    🔍 Line-by-Line Breakdown with Office-style Explanation

    ✅ What it does:

    Extracts numbers embedded in any text, whether the number is in Indian format (e.g., 2,50,000) or European format (e.g., 1.234,56).

    🎬 Scene-by-Scene Breakdown:

    🪪 Characters:

    • fromThis: The Excel cell that has the mixed content (like "Total ₹4,500.50 paid").
    • retVal: The string variable used to slowly build the extracted number.
    • european: A flag to detect if commas are used as decimal separators (common in European format like "1.234,56").

    💡 Step 1: Initialization

    vbaCopyEditretVal = ""
getNumber = 0
european = False

    Rohit clears any previous values and sets the assumption that the format is not European by default.

    🧠 Step 2: Detecting European Format

    vbaCopyEditIf fromThis.Value Like "*.*,*" Then
    european = True
End If

    This checks if the cell contains both a dot and a comma (e.g., "1.234,56"). If yes, it assumes the comma is the decimal point (European format).

    Priya’s vendor from Germany sent "1.250,50 EUR". This line sets european = True.

    🔁 Step 3: Loop Through Each Character

    vbaCopyEditFor i = 1 To Len(fromThis)
    ltr = Mid(fromThis, i, 1)

    The loop reads the text character by character. If the cell has "Amount ₹2,50,000.75", it starts reading "A", "m", "o", etc.

    🔢 Step 4: Build the Numeric Part

    Here’s the logic Rohit uses:

    vbaCopyEditIf IsNumeric(ltr) Then
    retVal = retVal & ltr

    If the character is a digit (0–9), it adds to the final number string.

    Then:

    vbaCopyEditElseIf ltr = "." And (Not european) And Len(retVal) > 0 Then
    retVal = retVal & ltr

    If it’s a . and it’s not European format, it’s added as the decimal point.

    vbaCopyEditElseIf ltr = "," And european And Len(retVal) > 0 Then
    retVal = retVal & "."

    If it’s European format, then the comma , is converted into a dot .—because VBA/Excel understand . as the decimal point.

    So "1.234,56" becomes "1234.56" internally.

    💾 Step 5: Convert the Final String to Number

    vbaCopyEditgetNumber = CDbl(retVal)

    Finally, the retVal string, say "4500.75", is converted into a Double data type using CDbl.

    🛑 Step 6: Error Handling

    vbaCopyEditOn Error GoTo last
...
last:
End Function

    If there’s any weird data or unexpected character that crashes the function, it fails silently and exits.

    📦 Examples: How It Works in Practice

    Cell ContentOutputExplanation
    "Rs. 4,500.75 paid"4500.75Indian format, plain extraction
    "1.234,56 EUR"1234.56European format, comma → dot
    "Amount: ₹2,50,000/-"250000Only digits picked, commas ignored
    "Advance of 7500.00 received"7500.00Straight number pulled out
    "Zero balance"0No digits found, returns 0

    ✅ Where to Use This Function

    Use =getNumber(A2) in any cell, where A2 contains your text with numbers.

    🎁 Bonus Tip from Rohit:

    You can paste this VBA code into your Excel file by pressing:

    1. ALT + F11 → Open VBA editor
    2. Insert > Module
    3. Paste the code
    4. Save as Macro-Enabled Workbook (.xlsm)

    Download Number Extraction VBA Function File

    Get NumbersDownload

    Top rated products

  • How to Convert Numbers to Words in Indian Rupees in Excel (With VBA)

    💰 How to Convert Numbers to Words in Indian Rupees in Excel

    Excel doesn’t have a built-in function to convert numbers to words (like “12345” → “Twelve Thousand Three Hundred Forty-Five”) — especially not in the Indian currency format like “Rupees Twelve Thousand Three Hundred Forty-Five Only”.

    But you can achieve this using a custom VBA function.

    ✅ Step-by-Step Guide: Convert Numbers to Words in Indian Rupees

    🧩 Step 1: Open VBA Editor

    1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
    2. Go to Insert > Module.

    ✍️ Step 2: Paste the VBA Code

    Paste the following code into the module window:

    Function ConvertToRupees(ByVal MyNumber)
    Dim Units As String
    Dim SubUnits As String
    Dim TempStr As String
    Dim Rupees As String
    Dim Paise As String
    Dim DecimalPlace As Integer
    Dim Count As Integer
    Dim Place(9) As String
    Dim NumDigit As Integer
    Dim Digit As Integer
    Dim MyNum As String
    Dim DecimalPart As String
    
    Place(2) = " Thousand "
    Place(3) = " Lakh "
    Place(4) = " Crore "
    
    ' Convert MyNumber to string and find position of decimal
    MyNumber = Trim(Str(MyNumber))
    DecimalPlace = InStr(MyNumber, ".")
    
    ' Split rupees and paise
    If DecimalPlace > 0 Then
        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If

    Count = 1
    Do While MyNumber <> ""
        If Count = 1 Then
            TempStr = GetHundreds(Right(MyNumber, 3))
            If Len(MyNumber) > 3 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 3)
            Else
                MyNumber = ""
            End If
        Else
            TempStr = GetTens(Right(MyNumber, 2))
            If Len(MyNumber) > 2 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 2)
            Else
                MyNumber = ""
            End If
        End If
        If TempStr <> "" Then Rupees = TempStr & Place(Count) & Rupees
        Count = Count + 1
    Loop

    ConvertToRupees = "Rupees " & Rupees & IIf(Paise <> "", " and " & Paise & " Paise", "") & " Only"
End Function

Private Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function

Private Function GetTens(TensText)
    Dim Result As String
    If Val(Left(TensText, 1)) = 1 Then
        Select Case Val(TensText)
            Case 10: Result = "Ten"
            Case 11: Result = "Eleven"
            Case 12: Result = "Twelve"
            Case 13: Result = "Thirteen"
            Case 14: Result = "Fourteen"
            Case 15: Result = "Fifteen"
            Case 16: Result = "Sixteen"
            Case 17: Result = "Seventeen"
            Case 18: Result = "Eighteen"
            Case 19: Result = "Nineteen"
            Case Else
        End Select
    Else
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "Twenty "
            Case 3: Result = "Thirty "
            Case 4: Result = "Forty "
            Case 5: Result = "Fifty "
            Case 6: Result = "Sixty "
            Case 7: Result = "Seventy "
            Case 8: Result = "Eighty "
            Case 9: Result = "Ninety "
            Case Else
        End Select
        Result = Result & GetDigit(Right(TensText, 1))
    End If
    GetTens = Result
End Function

Private Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    End Select
End Function

    🧪 Step 3: Use the Function in Excel

    After saving the VBA code:

    1. Go back to your Excel sheet.
    2. In any cell, enter:
    =ConvertToRupees(123456.78)
or
=ConvertToRupees(A2)

*** Here A2 Cell contains numbers

    Output:
    Rupees One Lakh Twenty Three Thousand Four Hundred Fifty Six and Seventy Eight Paise Only

    📌 Notes:

    • Works with Indian Numbering System (Thousand, Lakh, Crore).
    • You must enable macros to use this function.
    • Use it in billing templates, invoices, payment receipts, etc.

    📣 Learn More Excel Automation Like This!

    🎓 Join My Excel Mastery Course
    Includes advanced functions, automation using VBA, and business templates with Indian formats.

    ✅ Here is your ready-to-use Excel template to convert numbers to words in Indian Rupees:

    Convert_Numbers_to_Words_TemplateDownload

    📌 Important:

    This file includes:

    • Sample amounts in column A
    • A formula in column B using =ConvertToRupees(...)
    1. This is Macro enabled file. So you may get the warning. Just enabled the content if you are getting message in yellow and you are good to go.

    Breakdown of Excel VBA Function

    🔹 OVERVIEW OF THE MAIN FUNCTION: ConvertToRupees(ByVal MyNumber)

    This is the main function you call. It takes a numeric value (like 12345.50) and converts it into a full “Rupees … and … Paise” string.

    🔸 STEP 1: Variable Declarations

    vbaCopyEditDim Units As String, SubUnits As String, TempStr As String
Dim Rupees As String, Paise As String
Dim DecimalPlace As Integer, Count As Integer
Dim Place(9) As String
    • Rupees: Final string for rupee part.
    • Paise: Final string for decimal part.
    • Place(): Array for Indian numbering system—Thousand, Lakh, Crore, etc.
    • Count: Keeps track of digit positions (units, thousands, lakhs…).
    • TempStr: Stores interim word values as they’re built.
    vbaCopyEditPlace(2) = " Thousand "
Place(3) = " Lakh "
Place(4) = " Crore "

    🔸 STEP 2: Handle Decimal and Integer Split

    vbaCopyEditMyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
    • Converts the number to string, and finds the position of the decimal point (if it exists).
    • For example, 12345.50 becomes:
      • Rupee part: 12345
      • Paise part: 50
    vbaCopyEditIf DecimalPlace > 0 Then
    Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
    • Extracts the paise part using the GetTens function (explained later).
    • Ensures at least 2 digits by appending "00" and trimming.

    🔸 STEP 3: Main Loop to Build Words for Rupee Part

    vbaCopyEditCount = 1
Do While MyNumber <> ""
    • Processes the rupee part in blocks of 3 or 2 digits, depending on the Indian number system.
    vbaCopyEditIf Count = 1 Then
    TempStr = GetHundreds(Right(MyNumber, 3))
    • First time, take the last 3 digits (i.e., units, tens, hundreds).
    vbaCopyEditElse
    TempStr = GetTens(Right(MyNumber, 2))
    • From second iteration onwards, take 2 digits at a time (i.e., thousands, lakhs, crores).
    vbaCopyEditIf TempStr <> "" Then Rupees = TempStr & Place(Count) & Rupees
Count = Count + 1
    • Adds the word form to Rupees, prepending appropriate place name.

    🔸 STEP 4: Final Formatting

    vbaCopyEditConvertToRupees = "Rupees " & Rupees & IIf(Paise <> "", " and " & Paise & " Paise", "") & " Only"
    • Constructs the final string like:
      “Rupees Twelve Thousand Three Hundred Forty Five and Fifty Paise Only”

    🔹 SUPPORTING FUNCTIONS

    🔸 GetHundreds(MyNumber)

    • Converts a 3-digit number into words.
    • Example: "345" → "Three Hundred Forty Five"
    vbaCopyEditMyNumber = Right("000" & MyNumber, 3)
    • Pads numbers like "45" to "045" for consistent processing.
    vbCopyEditIf Mid(MyNumber, 1, 1) <> "0" Then
    Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    • If the hundred’s digit is not zero, convert it (e.g., 3 → “Three Hundred”).
    vbaCopyEditIf Mid(MyNumber, 2, 1) <> "0" Then
    Result = Result & GetTens(Mid(MyNumber, 2))
Else
    Result = Result & GetDigit(Mid(MyNumber, 3))
End If
    • Handles the remaining 2 digits using GetTens.

    🔸 GetTens(TensText)

    • Converts 2-digit numbers to words.
    • Example: "45" → "Forty Five", "19" → "Nineteen"
    vbaCopyEditIf Val(Left(TensText, 1)) = 1 Then
    Select Case Val(TensText)
        Case 10: Result = "Ten"
        Case 11: Result = "Eleven"
        ...
    • Handles special case of numbers from 10 to 19.
    vbaCopyEditElse
    Select Case Val(Left(TensText, 1))
        Case 2: Result = "Twenty "
        Case 3: Result = "Thirty "
        ...
    • Handles the tens place (20, 30, 40…).
    vbaCopyEditResult = Result & GetDigit(Right(TensText, 1))
    • Adds the unit place digit (e.g., 4 in 34).

    🔸 GetDigit(Digit)

    • Converts a single digit to its word form.
    • E.g., 1 → "One", 9 → "Nine"

    🔚 SUMMARY FLOW

    mathematicaCopyEditInput → "12345.50"
↓
Split to "12345" (Rupees) and "50" (Paise)
↓
Break into 3-2-2: 12 (Thousand), 345
↓
→ "Twelve Thousand Three Hundred Forty Five"
→ "Fifty Paise"
↓
Output → "Rupees Twelve Thousand Three Hundred Forty Five and Fifty Paise Only"

    Best selling products