Looker Studio Function : SUBSTR
SUBSTR Function in Looker Studio – Flexible Text Extraction Guide
Category: Text Function
The SUBSTR function in Looker Studio is a powerful text function used to extract a portion of text from a specified position within a string. Unlike LEFT_TEXT or RIGHT_TEXT, which work only from fixed ends, SUBSTR allows extraction from any position, making it far more flexible.
In real-world dashboards, data often comes as combined strings containing multiple pieces of information. Examples include order IDs with embedded dates, product codes with category indicators, or tracking parameters inside campaign names. SUBSTR helps analysts extract exactly what they need without restructuring the source data.
From a BI perspective, SUBSTR is widely used in calculated dimensions to create cleaner fields for grouping, filtering, and analysis. It is especially helpful when the text format is consistent and positional logic is known. Since the function works row by row, it performs efficiently even on large datasets.
Overall, SUBSTR is an essential text function for analysts who want precise control over text extraction directly inside Looker Studio.
Purpose of SUBSTR
1. Flexible Text Extraction
Extracts text from any position within a string, not limited to start or end.
2. Structured Data Parsing
Helps parse structured text values containing multiple embedded components.
3. Custom Dimension Creation
Creates calculated dimensions using specific portions of text fields.
4. Reduce Source Dependency
Avoids the need for source-level text splitting or preprocessing.
Type of Calculation & Practical Use Cases
1. Text Manipulation
Extracts specific characters from defined positions within text values.
2. Calculated Dimensions
Frequently used to build calculated dimensions for analysis and grouping.
3. Data Preparation
Acts as a lightweight data preparation tool inside Looker Studio.
4. Reporting Accuracy
Ensures only relevant text portions are used in reports.
SUBSTR(text, start_position, length)
SUBSTR Function Function – Parameters Table
| Parameter | Type | Description |
|---|---|---|
| text | Text | The text value to extract characters from |
| start_position | Number | Position where extraction begins (starting from 1) |
| length | Number | Number of characters to extract |
How Does the SUBSTR Work?
SUBSTR starts extracting characters from the specified start position and continues for the defined length. The function counts positions starting from 1, not 0.
If the requested length exceeds available characters, only remaining characters are returned. If the text value is NULL, the function returns NULL. Each row is processed independently, ensuring consistent results across datasets.
When Should You Use SUBSTR Function?
When text extraction is not limited to start or end
When working with structured text formats
When position-based extraction logic is known
When LEFT_TEXT or RIGHT_TEXT are insufficient
When creating precise calculated dimensions
Examples of SUBSTR Function
Example 1: Extract Year from Order ID
SUBSTR(Order_ID, 5, 4)
Example 2: Extract Middle Part of Product Code
SUBSTR(Product_Code, 3, 2)
Example 3: Extract Month from Date Text
SUBSTR(Date_Text, 6, 2)
Tips & Best Practices
Ensure text structure is consistent before using SUBSTR
Always verify start position and length values
Combine with LENGTH for dynamic logic
Test output in table views
Keep formulas simple and readable
No, SUBSTR only extracts characters and does not compare values, so character casing does not affect its behavior.
No, SUBSTR starts counting positions from 1, not zero, which is important when defining extraction logic.
No, numeric values must be converted to text before using the SUBSTR function.
If the start position exceeds text length, SUBSTR usually returns an empty or NULL result.
SUBSTR is simpler and faster for position-based extraction, while REGEXP_EXTRACT is better for pattern-based extraction.