Extract a single phone number

[Marcel_Tanbic]

Hey folks, need some help with regex… I have the following information and I just need the first telephone number. Is there a way to extract the information from Tel to Tel-Nr? Only need the first phone number of each record

[Abdullah_Yuksel]

If all the records are in the same format, this formula should work.
SUBSTITUTE(SUBSTITUTE(LEFT(fieldname,FIND(“Tel-Nr.:”, fieldname) - 1),“Tel.:”,“”),“.”,“”)

[Justin_Barrett]

Here’s a REGEX solution:

IF(Original, TRIM(REGEX_EXTRACT(Original, "(?:Tel.: )([^T]*)")))

[Marcel_Tanbic]

Justin_Barrett:

IF(Original, TRIM(REGEX_EXTRACT(Original, "(?:Tel.: )([^T]*)")))

Unfortunately it’s not working. :frowning:

[Justin_Barrett]

I can’t see the types of fields you’re working with in your screenshot. Is the source field a plain text field (single line or multiple line) or a lookup field? If it’s a lookup field, each instance of the source field needs to be forced into a string or it won’t calculate properly. For example, in the first formula I gave, you would need to change each instance of Original to Original & ""

On a side note, the single screenshot you provided earlier looks like it gave an incomplete sample of the kinds of patterns you’re working with based on your latest screenshot. Keeping in mind the lookup field fix just mentioned, try this variation, which should work more effectively for different kinds of patterns:

IF(Original, REGEX_EXTRACT(Original, "\\+\\d{2}\\s[^\\s]*\\s[^\\s]*"))

[Marcel_Tanbic]

Hey Justin, thanks a lot for the effort, but unfortunately It’s still not working. There are also a lot of spaces in between, from the scraping, you think, that might cause the error as well?

[Alexey_Gusev]

Hi,
just wanted to ask a common thing about regex.
If I use EXTRACT and the pattern not found, output is error. When I want it to display nothing, I need to use something like IF(ISERROR(REGEX_EXTRACT(Field, regex_function"),'',REGEX_EXTRACT(Field, "regex_function")).
Is there ‘more elegant way’ to do it? At least without function repeat

[Alexey_Gusev]

I tried and it works. Spaces should not affect it. Could you set row height to Tall and open Customize field with formula?

[Justin_Barrett]

Alexey_Gusev:

Is there ‘more elegant way’ to do it? At least without function repeat

You could use REGEX_MATCH() to confirm that the pattern exists first, then extract it, but that’s just repeating with a different function. Unfortunately that’s the only other option that comes to mind.

@Marcel_Tanbic I’ll echo the question from @Alexey_Gusev . Please take a screenshot of the formula field configuration showing the exact formula that you’re using. What I sent works on my end, so my guess is that there might be an issue with implementation. Did you copy and paste what I posted earlier, or did you retype it manually?