Extracting specific number sequences from text in Google Sheets

Other Questions Google Sheets
1

Help needed with Google Sheets data extraction

I’m working on a spreadsheet that contains rows like this:

Bought on May 3, 2022 Sale price:9875 Product @45678901

I’m trying to figure out how to get only the 8-digit numbers that start with the @ symbol. Is there a formula or method to do this? I want to ignore all the other text and numbers in each cell.

The tricky part is that the numbers I need always come at the end and have that special symbol in front. Any ideas on how to tackle this would be super helpful! Thanks in advance for your suggestions.

2

hey, you could try this:

=REGEXEXTRACT(A1, “@(\d{8})”)

it grabs the @ and 8 numbers after. works great for me when i need to pull out specific stuff from messy text. just paste it in the cell next to ur data and drag down. hope this helps!

3

I’ve faced a similar challenge when working with messy data from various sources. Here’s a robust solution that’s worked well for me:

Use this formula: =REGEXEXTRACT(A1, “@(\d{8})”)

It employs a regular expression to find the ‘@’ symbol followed by exactly 8 digits. This approach is more flexible and will work even if the position of the number changes within the cell.

One caveat: ensure your data consistently follows this pattern. If some entries deviate, you might need to add error handling or adjust the regex.

Also, consider using Data Validation to standardize input format in the future. It can save you a lot of headaches down the line.

Let me know if you need any clarification on implementing this!

4

To extract the 8-digit numbers starting with ‘@’, you can use a combination of RIGHT and FIND functions in Google Sheets. Here’s a formula that should work:

=RIGHT(A1, 8)

This assumes your data is in column A. The formula finds the ‘@’ symbol, then extracts the 8 characters to its right. If the ‘@’ symbol isn’t always in the same position, you might need to adjust the formula slightly.

For multiple cells, you can drag the formula down or use an arrayformula. Remember to handle potential errors if some cells don’t contain the expected format.

Hope this helps with your data extraction task!