如何在Google Data Studio中提取文本数字？REGEXP_EXTRACT返回null求助

Hey there! Let's troubleshoot why your regex formula is returning null when trying to extract numbers from text in Google Data Studio. Here are the most common issues and practical fixes:

1. Your regex doesn't account for common number formats #

Your current regex REGEXP_EXTRACT(X,"[0-9]*[0-9]") only matches positive integers. If your text includes decimals, negative numbers, or numbers with commas, it won't capture them, leading to null results.

Adjusted regex patterns for different scenarios: #

• For positive integers or decimals (e.g., 123 or 45.67):

  REGEXP_EXTRACT(X, "[0-9]+(\\.[0-9]+)?")
  

• For positive/negative integers or decimals (e.g., -89 or 101.23):

  REGEXP_EXTRACT(X, "-?[0-9]+(\\.[0-9]+)?")
  

• For numbers with commas (e.g., 1,234 or 5,678.90):

  REGEXP_EXTRACT(X, "-?[0-9]+(,[0-9]+)*(\\.[0-9]+)?")
  

2. The field X isn't a text type #

Google Data Studio's REGEXP_EXTRACT only works reliably on text fields. If your field X is a numeric type (like number or currency), the regex can't process it correctly. Convert it to text first using CAST:

REGEXP_EXTRACT(CAST(X AS TEXT), "[0-9]+")

3. Numbers are embedded in non-standard text #

If numbers are wrapped in special characters or prefixed/suffixed with text (e.g., Price: $99 or Order #123), you need to tweak the regex to target the number specifically. For example, to extract numbers after a colon:

REGEXP_EXTRACT(X, ":\\s*([0-9]+)")

The parentheses here ensure only the number is captured, ignoring the preceding text and whitespace.

4. Stick to RE2 syntax #

Google Data Studio uses RE2 regex rules, which don't support advanced features like lookbehind assertions. Keep your patterns simple and RE2-compliant to avoid unexpected nulls.

Test these adjustments with your actual text data—one of these fixes should resolve the null result issue!

内容的提问来源于stack exchange，提问作者Asaduzzaman Robin