Cleaning Mixed Data in Tableau: How to Isolate Phone Numbers and Address with REGEXP_EXTRACT

When working with real-world data, it’s common to encounter fields that contain multiple types of information crammed into one column. A classic example? A column that combines addresses and phone numbers—like this :

6649 N Blue Gum St (504) 621 8927 

4 B Blue Ridge Blvd (810) 292 9388 

8 W Cerritos Ave #54 (856) 636 8749 

...

This may not look too bad at first, but to a data analyst, this is trouble waiting to happen.

When This Happens in the Real World ?

You’ll see this a lot when:

• Importing data from legacy systems

• Scraping web pages or PDFs

• Receiving manual input from customers or employees

It’s one of the key tasks in data cleaning, which typically takes up 60–80% of a data analyst’s time.

What is REGEXP_EXTRACT?

REGEXP_EXTRACT is a Tableau function that allows you to extract substrings from a text field using regular expressions (regex)—a pattern-matching syntax used to identify specific character sequences.

In our case, we’ll use it to extract phone numbers in this format:

Step-by-Step : Extracting Data

1.      Load Your Data into Tableau:

I have loaded the data as below which has a column combined with Address and Phone number.

image by author

2.      Create a Calculated Field

·       Go to the Data Pane in Tableau.

·       Right-click on your data source.

·       Select Create Calculated Field.

·       Name it something like: Phone Number

image by author

 3.      Enter This Formula:

Depending on your Tableau version, you might need to use double backslashes:

Here’s what the pattern means:

·       \( and \) match literal parentheses.

·       [0-9]{3} matches exactly 3 digits — the area code.

·       [0-9]{4} matches 4 digits — the line number.

·       Spaces are matched literally.

So the pattern matches phone numbers like: (504) 621 8927

image by author

4.      Drag and drop the Phone Number in rows as below:

We have successfully extracted the phone number from the data

image by author

Want to test and explore this regex pattern interactively?

You can use one of the following online tool to paste your data and try the

 regex yourself: RegExr – very beginner-friendly, with visual feedback and syntax help.

These tools are especially helpful when you're learning regex or troubleshooting a pattern.

Tip: Copy your data and use Regex expressions to test the pattern of your data. It will highlight the data as you type.

Using the same to extract the Address

To extract just the address part, i.e., everything before the phone number, use:

How This Works

·       ^ – Starts at the beginning of the string

·       (.*) – Captures everything (the address)

·       (?= ...) – A lookahead: ensures the address ends right before the phone number

·       The phone number regex ensures we stop capturing once we reach it

image by author

Final Thoughts

REGEXP_EXTRACT in Tableau makes it easy to clean and separate messy data like combined address and phone fields. With just a little regex, you can isolate useful elements and turn unstructured text into clean, structured fields. You can follow the above method for extracting ZIP codes, email addresses, or handling inconsistent formats!

Pro Tip

Use regex in Tableau when:

• Your data source mixes multiple data types in one column

• You need to extract specific text patterns

• You want to avoid manual cleanup or Excel preprocessing

  
  

Thank you for Reading!