n8n: Formatting Numbers Before Importing to Google Sheets

Automating workflows is a superpower for modern businesses, saving countless hours and reducing manual errors. Tools like n8n have revolutionized how we connect applications and move data between them, offering incredible flexibility for founders, marketers, and tech enthusiasts alike. You can explore powerful automation possibilities with n8n. However, even the most sophisticated automation can stumble on a surprisingly common hurdle: inconsistent data formatting. This is particularly true when dealing with numbers sourced from various places, like invoices or user inputs, destined for structured environments like Google Sheets. If you’ve ever pulled your hair out trying to figure out why Google Sheets sees your imported revenue figures as plain text, you’re in the right place. This guide dives deep into a specific, frequent challenge: n8n: Formatting Numbers Before Importing to Google Sheets, ensuring your data flows smoothly and accurately.

The Common Culprit: Inconsistent Number Formats

Imagine you’re building an n8n workflow to automatically extract data from vendor invoices and populate a Google Sheet for financial tracking. The dream is seamless data entry, but reality often bites. You check your Google Sheet, and instead of neat columns of numbers ready for calculation, you find a mess. Some values are left-aligned (a sign Sheets sees them as text), others are wildly incorrect, and your SUM formulas are returning errors.

What’s going on? The problem lies in the diverse ways numbers are represented across different documents, regions, and systems:

• Decimal Separators: Is it 123.45 (common in the US/UK) or 123,45 (common in many European countries)?
• Thousands Separators: Do you see 1,234.56 or 1.234,56? Some sources might not use one at all.
• Currency Symbols: Values might appear as $99.99, €1.500,00, or £5,000.
• Hidden Characters: Sometimes, non-visible characters like non-breaking spaces can sneak in.

Google Sheets, aiming for consistency, typically expects numbers in a clean, unambiguous format. By default, in many English-language settings, it prefers a period (.) as the decimal separator and no thousands separators or currency symbols (e.g., 1234.56). When it encounters something like $909,900.00 or 1.234,56, it often gets confused and defaults to treating the value as plain text, rendering it useless for calculations.

Importing inconsistently formatted numbers results in Google Sheets treating values as text or misinterpreting them entirely.

The n8n Community Experience

Why n8n is Perfect for Tackling This

This is precisely where the power of a workflow automation tool like n8n shines. Instead of trying to clean the data *after* it lands (or fails to land correctly) in Google Sheets, n8n allows you to intercept and transform the data mid-flight. Its expression editor, powered by JavaScript, provides robust tools for data manipulation directly within your workflow nodes.

You don’t need complex external scripts or separate cleaning steps. You can build the formatting logic right into the connection point before the data ever reaches Google Sheets. This keeps your workflow self-contained, easier to manage, and ensures the data arriving at its destination is pristine and ready for use.

The Solution: JavaScript replaceAll() in n8n Expressions

The most direct and efficient method within n8n to standardize these varying number formats is by using JavaScript’s built-in `replaceAll()` string function. This function allows you to find all occurrences of a specific character (or string) within your data and replace them with another character (or remove them entirely). By strategically chaining these `replaceAll()` calls together within an n8n expression, you can systematically strip away unwanted characters and standardize separators.

Why `replaceAll()`? It’s simple, fast, and directly addresses the problem without needing external libraries or the potential complexities and costs associated with using AI models for such a deterministic task. While AI tools have their place, using them for basic character substitution is like using a sledgehammer to crack a nut – effective, maybe, but inefficient and overkill.

Step-by-Step Guide to Formatting Numbers in n8n

Let’s walk through how to implement this solution in your n8n workflow, specifically targeting the Google Sheets node where you’re sending the numerical data.

Step 1: Locate the Target Field Mapping

Open your n8n workflow and navigate to the Google Sheets node responsible for adding or updating rows. In the node’s parameters panel, find the specific field where you are mapping the problematic numerical data. This might be labeled ‘Amount’, ‘Total’, ‘Valor total’, ‘Price’, etc., depending on your source data and sheet structure.

Step 2: Access the Expression Editor

Next to the value input area for that target field, you’ll see a small ‘fx’ button or an “Add Expression” option. Click on this. This opens the n8n expression editor, allowing you to use JavaScript and n8n’s specific syntax to manipulate the incoming data.

Step 3: Build Your Cleaning Expression with replaceAll()

This is where the magic happens. You’ll reference the incoming data field (e.g., using `{{ $json[‘YourSourceFieldName’] }}`) and chain `replaceAll()` methods to clean it up. The order of operations can be crucial, especially when dealing with formats where ‘.’ and ‘,’ might swap roles.

Here’s a common scenario: cleaning numbers like $1,234.56 or £999.00 to the Google Sheets-friendly format 1234.56 or 999.00.

Sub-Step 3.1: Remove Currency Symbols

Start by removing any currency symbols. You might need multiple `replaceAll` calls if you expect different currencies.

Example: Removing ‘$’

{{ $json['Valor total'].replaceAll('$', '') }}

If you also need to handle Euros (€) or Pounds (£), chain them:

{{ $json['Valor total'].replaceAll('$', '').replaceAll('€', '').replaceAll('£', '') }}

At this point, $1,234.56 becomes 1,234.56.

Sub-Step 3.2: Remove Thousands Separators (Comma Example)

Next, remove the thousands separator. Assuming it’s a comma (,) in this format:

{{ $json['Valor total'].replaceAll('$', '').replaceAll('€', '').replaceAll('£', '').replaceAll(',', '') }}

Now, 1,234.56 becomes 1234.56. This is the format Google Sheets typically understands perfectly as a number.

Complete Expression for inputs like $909,900.00:

{{ $json['Valor total'].replaceAll('$', '').replaceAll(',', '') }}

Result: 909900.00

Sub-Step 3.3: Handling Alternate Formats (Period Thousands, Comma Decimal)

What if your source uses the format 1.234,56 (common in Germany, for example)? The previous expression would incorrectly produce 1234,56, which Sheets might still misinterpret. Here, the order is critical:

1. Remove the thousands separator (the period ‘.’).
2. Replace the decimal separator (the comma ‘,’) with a period (‘.’).

The expression looks like this:

// For inputs like 1.234,56 or € 9.999,99{{ $json['YourSourceFieldName'].replaceAll('€', '').replaceAll(' ', '').replaceAll('.', '').replaceAll(',', '.') }}

Let’s trace € 9.999,99:

1. replaceAll('€', '') -> 9.999,99
2. replaceAll(' ', '') -> 9.999,99 (removes potential spaces)
3. replaceAll('.', '') -> 9999,99 (removes thousands separator)
4. replaceAll(',', '.') -> 9999.99 (changes decimal comma to period)

Result: 9999.99 – Ready for Google Sheets!

Step 4: Apply and Test the Expression

Paste your final, chained `replaceAll()` expression into the expression editor for the relevant field in your Google Sheets node. Close the editor. Now, run your workflow with sample data that includes the various inconsistent formats you anticipate. Check the output in your Google Sheet to confirm that the numbers are appearing correctly and are recognized as numerical values (usually right-aligned by default in Sheets).

Practical Use Cases and Examples

Let’s see this technique in action across different scenarios:

Use Case 1: Processing Scanned Invoices

You use an OCR (Optical Character Recognition) tool or service (perhaps even one integrated via n8n!) to extract data from PDF invoices. These invoices come from various suppliers globally.

• Input Data Field: `InvoiceTotal`
• Sample Raw Inputs: `$1,500.75`, `€ 999,00`, `£ 2,100.50`, `875.20`
• Goal: Standardize to `XXXX.XX` format for Google Sheets.
• n8n Expression (assuming destination uses ‘.’ decimal):

  {{ $json.InvoiceTotal.replaceAll('$', '').replaceAll('€', '').replaceAll('£', '').replaceAll(',', '').replaceAll(' ', '') }} // Basic version if comma is only ever thousands sep.

  Or, a more robust version handling both `.` and `,` as potential thousands separators, *assuming* the final separator is the decimal:

  // Step 1: Remove currency & spacesvar clean = $json.InvoiceTotal.replaceAll('$', '').replaceAll('€', '').replaceAll('£', '').replaceAll(' ', '');// Step 2: Check last separator - assumes it's the decimalvar lastComma = clean.lastIndexOf(',');var lastDot = clean.lastIndexOf('.');// If comma is last or only separator, assume it's decimalif (lastComma > lastDot) {  clean = clean.replaceAll('.', '').replaceAll(',', '.'); // Remove dots, change comma to dot} else if (lastDot > lastComma) {  clean = clean.replaceAll(',', ''); // Remove commas, keep dot} else {   // No separators or only one type - assume standard US/UK if unsure or handle error   clean = clean.replaceAll(',', '');}return clean;

  Note: The second example uses a more complex expression, potentially better suited for a Function node before the Google Sheet node for clarity, but demonstrates handling more ambiguity directly. Simple `replaceAll` chains are preferred if your formats are predictable.

• Output in Google Sheet: `1500.75`, `999.00`, `2100.50`, `875.20` (all correctly recognized as numbers).

Use Case 2: Consolidating E-commerce Sales Data

You run online stores targeting different regions (e.g., US and Germany) using platforms like Shopify or WooCommerce. You pull sales reports via API into n8n to create a central dashboard in Google Sheets.

• Input Data Field: `order_total`
• Sample Raw Inputs: `99.99` (from US store), `1.249,95` (from DE store)
• Goal: Standardize to `XXXX.XX` format.
• n8n Expression (DE format requires conversion):

  // Let's assume we know which store data comes from via another field, e.g., $json.storeRegionvar value = $json.order_total;if ($json.storeRegion == 'DE') {  // Format is likely 1.234,56 - remove dots, change comma to dot  return value.replaceAll('.', '').replaceAll(',', '.');} else {  // Assume US/UK format 1,234.56 - remove commas  return value.replaceAll(',', '');}

  (This example uses conditional logic based on another data point, often necessary for truly mixed sources).

• Output in Google Sheet: `99.99`, `1249.95`

Use Case 3: Cleaning Numerical Survey Responses

You collect data via online forms (Typeform, Google Forms), asking for numerical input like budget estimates or ratings. Users might enter values like “1,000”, “$500”, or “10.000” (meaning ten thousand in some regions).

• Input Data Field: `budget_estimate`
• Sample Raw Inputs: `1,000`, `$500`, `250.99`, `10.000`
• Goal: Get clean numbers like `1000`, `500`, `250.99`, `10000`.
• n8n Expression (handling both separators, assuming ‘.’ decimal target):

  // More robust approach for mixed user input:var clean = $json.budget_estimate.replaceAll('$', '').replaceAll(' ', '');// Check if comma exists AND period existsvar hasComma = clean.includes(',');var hasDot = clean.includes('.');if (hasComma && hasDot) {    // Ambiguous - assume format based on last separator?    if (clean.lastIndexOf(',') > clean.lastIndexOf('.')) {        // Assume 1.234,56 style        clean = clean.replaceAll('.', '').replaceAll(',', '.');    } else {        // Assume 1,234.56 style        clean = clean.replaceAll(',', '');    }} else if (hasComma) {    // Only comma exists - could be 1,234 or 123,45? Treat as thousands? Safer to remove. Or treat as decimal?    // Let's assume comma is thousands if no dot present, OR decimal if it's near end? Risky.    // Safest bet for mixed input might be: remove thousands, convert potential decimal comma    clean = clean.replaceAll('.', ''); // Remove any dots first (if 10.000 meaning 10k)    clean = clean.replaceAll(',', '.'); // Convert comma to dot (if 123,45) - this is flawed logic!    // Better: Define a clear rule. Let's assume comma is thousands if no dot.    clean = $json.budget_estimate.replaceAll('$', '').replaceAll(' ', '');    if (clean.includes('.')) { // If dot exists, assume it's decimal, remove commas       clean = clean.replaceAll(',', '');    } else { // No dot exists, assume comma is thousands separator (or part of integer)       clean = clean.replaceAll(',', ''); // Just remove comma here too for simplicity    }     // Re-run with 10.000 and 1,000:     // For "10.000" -> no dot check needed, remove comma -> "10000"     // For "1,000" -> no dot, remove comma -> "1000"     // For "$500" -> no dot/comma, remove $ -> "500"     // For "250.99" -> has dot, remove comma -> "250.99"     // Need a clearer rule for `10.000` vs `10,000` if both mean 10k.     // Final attempt focusing on GOAL (get number for sheets): Remove ALL non-numeric EXCEPT last dot/comma?     // Simplest approach: Remove common separators NOT used for decimals in target format.     // Target: 1234.56     clean = $json.budget_estimate.replaceAll('$', '').replaceAll(',', '').replaceAll(' ', ''); // Remove $, ,, space} else {    // Only dot exists or neither - do nothing to separators    clean = clean.replaceAll('$', '').replaceAll(' ', '');}return clean;// *** RECOMMENDED SIMPLER APPROACH FOR MOST CASES (TARGET '.' decimal) ***// Remove common non-numeric/non-decimal chars first.{{ $json.budget_estimate.replaceAll('$', '').replaceAll(' ', '').replaceAll(',', '') }}// This works for $500 -> 500, 1,000 -> 1000, 250.99 -> 250.99. It FAILS for 10.000 meaning 10k -> 10.000// If `10.000` actually means `10k`, you need the more complex logic:{{ $json.budget_estimate.replaceAll('$', '').replaceAll(' ', '').replaceAll('.', '').replaceAll(',', '.') }} // Handles 1.234,56 -> 1234.56

• Output in Google Sheet: `1000`, `500`, `250.99`, `10000` (assuming the expression correctly handles the intended meaning of `10.000`). User input is notoriously messy!

Important Considerations and Potential Pitfalls

While `replaceAll()` is powerful, keep these points in mind:

Ambiguity is Your Enemy

The biggest challenge arises when your source data is truly ambiguous. If the *same data source* might provide 1,234.56 (comma thousands, dot decimal) and 1.234,56 (dot thousands, comma decimal) completely unpredictably for the same field, a simple chain of `replaceAll()` will inevitably fail for one of the formats. In these rare but frustrating cases, you might need more sophisticated logic:

• Code Node: Use an n8n Code node before the Google Sheets node to write more complex JavaScript logic (like checking the position of the last separator) to reliably determine the intended format before cleaning.
• Regular Expressions (Regex): The `replace()` function (note: not `replaceAll` initially, though newer JS supports Regex in `replaceAll`) can accept regular expressions, allowing for more complex pattern matching and replacement. This has a steeper learning curve but offers more power for ambiguous cases.

However, for most common scenarios where formats are inconsistent *between* sources but consistent *within* a single source type (or follow predictable regional patterns), chained `replaceAll()` is sufficient and much simpler to implement.

Apply Formatting Per Field

Remember that this cleaning expression needs to be applied individually within the n8n Google Sheets node for every single numerical field that might suffer from inconsistent formatting. Don’t assume one expression fits all columns; analyze the potential inputs for each.

Test, Test, Test!

Before letting your automation run wild, thoroughly test your workflow with a diverse range of sample inputs that cover all the formatting variations you expect to encounter. Create test rows in your Google Sheet specifically for verifying the output. Check edge cases: very large numbers, numbers without decimals, numbers with leading/trailing spaces (add `.trim()` to your expression chain if needed!), and zero values.

Google Sheets Formatting vs. Data Cleaning

It’s crucial to distinguish between cleaning the data *before* import and applying visual formatting *within* Google Sheets. The goal in n8n is to send a clean, raw numerical value (e.g., 1234.56) that Google Sheets recognizes as a number. Once it’s correctly imported as a number, you can use Google Sheets’ own formatting options (Format > Number) to display it with currency symbols, thousands separators, or specific decimal places for presentation purposes. Don’t try to add currency symbols or thousands separators back in within the n8n expression if Sheets expects the raw number.

Revisiting the AI Approach

As mentioned in the n8n community thread that inspired this discussion, using an AI node (like OpenAI) was suggested. While technically possible (you could prompt an AI to “convert this string to a standard number format”), it’s generally not the right tool for this job. Why?

• Inefficiency: Calling an external AI service for simple string replacement is computationally expensive and much slower than a built-in function.
• Reliability: AI responses can sometimes be unpredictable or inconsistent, especially with edge cases. String functions are deterministic.
• Cost: AI services often have associated costs per call, whereas built-in functions are free.

Save AI for tasks requiring genuine understanding, interpretation, or generation, not for rule-based string manipulation.

Level Up Your Automation with Clean Data

Mastering data cleaning techniques like this within n8n is fundamental to building robust and reliable automations. Dirty data is often the weakest link in any workflow. By proactively addressing number formatting issues before they reach Google Sheets, you ensure data integrity, prevent errors, and unlock the full potential of your automated reporting and analysis.

Taking the time to understand potential inconsistencies and implement simple, effective solutions like chained `replaceAll()` expressions elevates your automation skills and saves significant troubleshooting time down the line. It’s these small details that separate fragile workflows from dependable automation engines.

Conclusion: Take Control of Your Numbers

Dealing with inconsistent number formats when moving data into Google Sheets is a frequent pain point for n8n users. Thankfully, the solution is readily available within n8n itself. By leveraging JavaScript’s `replaceAll()` function within expressions in your Google Sheets node, you can effectively strip away unwanted characters like currency symbols and thousands separators, and standardize decimal points.

This guide on n8n: Formatting Numbers Before Importing to Google Sheets has shown you the exact steps and considerations involved. Remember to analyze your potential input formats, chain your `replaceAll()` calls logically, apply the expression to each relevant field, and test thoroughly.

Stop letting messy numbers derail your automations. Implement these formatting techniques today and ensure your data flows cleanly and accurately into Google Sheets every time. Ready to build more powerful and reliable workflows? Explore the possibilities with n8n and keep refining your automation skills!