求助：如何用Excel公式格式化多种格式数字以开展计算

Got it, let's tackle this messy number formatting problem step by step. You've got a mix of US-style, European-style, space-separated thousand separators, and even numbers with trailing text—here's how to convert all of them into consistent, computable numeric values.

Step 1: Extract Numeric Substrings #

First, we need to strip out any non-numeric characters (except for commas, dots, and spaces that act as separators). Use regex to pull out the core numeric part from strings like 1,234,532.67 some text.

import re

def extract_numeric_part(s):
    # Match sequences of digits, commas, dots, and spaces (ignore other characters)
    match = re.search(r'[\d.,\s]+', str(s))
    if match:
        return match.group().strip()
    return None

Step 2: Detect Format & Normalize to Standard Float #

Next, we need to figure out if the number uses a comma or dot as the decimal separator (since thousand separators can be commas, dots, or spaces). The key clue is the last occurrence of a comma/dot—this will be the decimal separator, while earlier ones are thousand separators.

def normalize_number(num_str):
    if not num_str:
        return None
    
    # Replace space thousand separators with empty string first
    num_str = num_str.replace(' ', '')
    
    # Count commas and dots to determine format
    comma_count = num_str.count(',')
    dot_count = num_str.count('.')
    
    # Case 1: Only commas (either thousand separators + decimal, or just decimal)
    if dot_count == 0:
        # If more than one comma, last is decimal; others are thousand separators
        if comma_count > 1:
            return float(num_str.rsplit(',', 1)[0].replace(',', '') + '.' + num_str.rsplit(',', 1)[1])
        # Single comma = decimal separator
        else:
            return float(num_str.replace(',', '.'))
    
    # Case 2: Only dots (either thousand separators + decimal, or just decimal)
    elif comma_count == 0:
        # If more than one dot, last is decimal; others are thousand separators
        if dot_count > 1:
            return float(num_str.rsplit('.', 1)[0].replace('.', '') + '.' + num_str.rsplit('.', 1)[1])
        # Single dot = decimal separator
        else:
            return float(num_str)
    
    # Case 3: Both commas and dots
    else:
        # Whichever comes last is the decimal separator
        if num_str.rfind('.') > num_str.rfind(','):
            # Dot is decimal, commas are thousand separators
            return float(num_str.replace(',', ''))
        else:
            # Comma is decimal, dots are thousand separators
            return float(num_str.replace('.', '').replace(',', '.'))

Step 3: Combine Functions for Full Processing #

Put it all together to process your entire list of values:

sample_data = [
    "1,234,532.67", "2.345,43", "2 345,43", "2 345.43",
    "65820,6", "1.435", "123.45", "12,534.34",
    "1,343", "2,435.45", "321,45", "1,234,532.67 some text"
]

processed_values = []
for item in sample_data:
    num_part = extract_numeric_part(item)
    normalized = normalize_number(num_part)
    processed_values.append(normalized)

print(processed_values)
# Output:
# [1234532.67, 2345.43, 2345.43, 2345.43, 65820.6, 1.435, 123.45, 12534.34, 1343.0, 2435.45, 321.45, 1234532.67]

Key Notes #

• This handles all your sample formats, including space-separated thousand separators and text-embedded numbers.
• If you're working in Excel/Google Sheets instead of Python, you can adapt the logic using formulas:
  • Use REGEXEXTRACT to pull numeric parts.
  • Use SUBSTITUTE to replace separators based on position checks (e.g., IF(FIND(".", A1) > FIND(",", A1), SUBSTITUTE(A1, ",", ""), SUBSTITUTE(SUBSTITUTE(A1, ".", ""), ",", "."))).

内容的提问来源于stack exchange，提问作者Andrei Mazilu