Excel macro – Training By Himanshu

🧾 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 Content	Output	Explanation
`"Rs. 4,500.75 paid"`	`4500.75`	Indian format, plain extraction
`"1.234,56 EUR"`	`1234.56`	European format, comma → dot
`"Amount: ₹2,50,000/-"`	`250000`	Only digits picked, commas ignored
`"Advance of 7500.00 received"`	`7500.00`	Straight number pulled out
`"Zero balance"`	`0`	No digits found, returns 0