Regex_replace to format phone numbers

[EmmaAtKiwi]

Hi! Maybe you'll know the answer to this Ops Hub question @karstenkoehler...

I want to format contact phone numbers from 8004567890 or 18004567890 to +1 (800) 456-7890.

I created a workflow and added the Format Data step. According to this developer doc, I should write the expression like this:

• {{ "[phone]"|regex_replace(\d{3})(\d{3})(\d{4})', '$1-$2-$3') }}

That, however, returns an error. So I rewrote the expression according to how the workflow wanted it, and it indicated no issues:

• regex_replace([phone],'^\D*1?', '+1'), '(\d{3})(\d{3})(\d{4})', '$1-$2-$3')

It didn't work though 🙁 Running the workflow returns the error 

So how are we to write this expression? Why is it required in a different format compared to HubDB?

[SteveHTM · Accepted solution]

Hu @EmmaAtKiwi - I may be confused, but it looks to me that you are trying to use the regex function in a workflow format step. But the reference you are making to the usage docs is for a HubL function. 

I know very well that the differences/inconsistencies between HubSpot tools and different conexts can be maddening sometimes.

The (hard to search for) list of avalable formulas for the workflow context is at: https://knowledge.hubspot.com/workflows/custom-formula-functions - I think you can do what you want with "replace" - but I haven't tried specifically.

Steve

[DorianMcFarland · Accepted solution]

Hi @EmmaAtKiwi,

I was struggling with a similar issue. After many hours of frustration I found what appears to be a bug in the data formatting implementation of regex_replace. I found that although the editor was accepting my input variable as a valid type, somewhere later down the line it was rejecting it. By forcing/coerding the input value into a string using another function I got it to work (I used "cut()" with no value). In my case I'm taking an 8 digit phone number and adding a country code:

regex_replace(cut([phone], ""), "^(\d{8})$", "+47$1")

In your case you'd want something like:

regex_replace(cut([phone], ""), "(\d{3})(\d{3})(\d{4})", "+1 $1-$2-$3")

Here's the docs for the supported regex syntax: https://github.com/google/re2/wiki/Syntax

Although the workflow accepts regex_replace as a valid function it's not listed in the official documentation for custom formula functions:
https://knowledge.hubspot.com/workflows/custom-formula-functions

Hope that helps!

[Clare_HS]

I know this thread is a little older, but I also know how frustrating it can be to not find a solution! This is a bit more advanced, but I've found it works really well for what you're trying to accomplish, @EmmaAtKiwi., and includes some instructions to help give context. You want the cca.js file to use as a custom code action instead of a formula...

Gives two options for formatting US phone numbers. To get the +1, I just modifed line 74 slightly:

return '+1 ' + '(' + match[1] + ') ' + match[2] + '-' + match[3];

Here are two other screenshots that should help:

And...

@AntoineBr, looks like this is yours, so I hope you don't mind the respost. And THANK YOU for all the time you're diligence will save me!!

[SteveHTM · Accepted solution]

Hu @EmmaAtKiwi - I may be confused, but it looks to me that you are trying to use the regex function in a workflow format step. But the reference you are making to the usage docs is for a HubL function. 

I know very well that the differences/inconsistencies between HubSpot tools and different conexts can be maddening sometimes.

The (hard to search for) list of avalable formulas for the workflow context is at: https://knowledge.hubspot.com/workflows/custom-formula-functions - I think you can do what you want with "replace" - but I haven't tried specifically.

Steve

[EmmaAtKiwi]

Hi @SteveHTM! You are correct, I AM trying to format phone numbers in a workflow, but can not figure out the correct syntax structure.

The developer docs I referenced indicated I could write the syntax like this -- 

{{ "[phone]"|regex_replace(\d{3})(\d{3})(\d{4})', '$1-$2-$3') }} -- but the workflow format step returned an error.

So how the heck am I supposed to write it, if the HubSpot workflow doesn't seem to speak the same language as the HubSpot developer docs?!?!? 😩

[SteveHTM]

@EmmaAtKiwi - I think my point is that your function reference is to a HubL document which really is a different language to the custom formula seup in workflow steps. So you can't mix and match thos functions. HubL functions can only be used in page markup/HTML scripting etc.

Hope that this makes sense. Apologies if I am getting teh context incorrect.

Steve

[EmmaAtKiwi]

Thank you @SteveHTM, you're absolutely getting it correct, it's ME that's clearly struggling! So if my syntax deduced from the HubL doc won't work, do you know what will?

[BérangèreL]

Hi @EmmaAtKiwi,

I understand how frustrating this can be.

I'd like to ask our Top Experts: Hi @MBERARD, @HFisher7 and @louischausse do you have any tips to help @EmmaAtKiwi, please?

@EmmaAtKiwi, if you don't get an answer here, since this is quite technical, I recommend that you post in the Developers Forum here.

Thank you and have a great day!

Best,
Bérangère

[EmmaAtKiwi]

 Thanks @BérangèreL! Unfortunately, clicking on "show details" does not, in fact, give me any details... The error message only reiterates that I wrote the syntax wrong, but I don't know where!

[Expertopinionsa]

Hey there!

I get it, I think the expression you're using is almost there but needs a little tweak. Try this instead

{{ "[phone]"|regex_replace('^\D*1?(\d{3})(\d{3})(\d{4})', '+1 ($1) $2-$3') }}

This will  take care of formatting numbers like 8004567890 or 18004567890 into +1 (800) 456-7890.

The changes I made is adjusting the placement of the capturing groups and fixing the pattern for the optional '1'. Give it a shot, and hopefully, it solves the problem for you.

I would love know how it goes on triotech systems! If you still run into issues, we can dive deeper into troubleshooting. Good luck! 

[EmmaAtKiwi]

Thanks @Expertopinionsa! I'm still getting an error though, since HubSpot CRM using this function differently than HubSpot CMS, it seems.

It doesn't like the {{... but when I remove the brackets, it doesn't like |... even though  that's how they syntax is meant to be written! 

[Expertopinionsa]

Give it a shot and let me know if it works this time. Fingers crossed! 🤞 If not, we can keep tweaking until we nail it.

"Phone number " regex_replace('^\D*1?(\d{3})(\d{3})(\d{4})', '+1 ($1) $2-$3')

[EmmaAtKiwi]

No luck yet 🙁

[BérangèreL]

Hi @EmmaAtKiwi, I hope that you are well!

I have limited experience with coding so I'd like to ask some Top Experts to take a look at this: Hi @karstenkoehler, @KimRogers, @SR0, @Ntbrown, @Gonzalo  and @jpsanchez do you have suggestions to help @EmmaAtKiwi on this, please?

Thank you very much ❤️ and have a nice day!

Best,
Bérangère

[BérangèreL]

Hi @EmmaAtKiwi 👋, I hope that you are keeping well!

Thank you for asking the Community, great question!

I understand that you are using a custom coded workflow action to format phone numbers but you get an error. If that's not correct, please let me know.

- When you click on "show details" regarding the error message, what does it say, please?
The more info, screenshots (without sensitive/confidential information), and details you can provide, the better the Community can assist.

I'd like to invite a couple of subject matter experts to this conversation: Hi @louischausse, @ChrisoKlepke and @Teun do you have suggestions to help @EmmaAtKiwi, please?

Also, if anybody else has anything to add and/or share, please feel free to join in the conversation 🙂

Thank you very much for sharing your expertise ❤️ and have a wonderful day!

Best,
Bérangère