Best answer

Error: Failed to create a text transform in Formatter

  • 9 February 2023
  • 5 replies
  • 180 views

Userlevel 1
Badge

Hi,

I’m trying to use a formula I use in spreadsheets to split up a UK postcode. I’ve copied the formula into the format tool as shown below. However i get a failed return off this. Any ideas?
 

 

icon

Best answer by SamB 9 February 2023, 16:54

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

5 replies

Userlevel 7
Badge +11

Hey there @otimo! 👋

I think the issue is that REGEXEXTRACT isn’t one of the supported functions. You can view a full list of the supported Spreadsheet-Style Formula functions here: Formula Functions

Formatter does have a Split Text transform but, as the letters and numbers don’t have anything in between them, you might have better luck using it’s Extract Pattern as that uses regex. Although, you can’t use your existing formula for it. You’d need to select the postcode field from HubSpot as the Input field then enter the regex pattern into the Pattern field.

For example, if you were looking to extract just the first two characters from the postcode you could use ^[\s\S]{0,2} which would look something like this:

b9a1091ebea9f53c68df3dc779a708e3.png
That would output the first 2 characters into a separate field that you could then select in any subsequent actions. For example:
552152ce359c549cf72d1cb9110db63a.png

Hope that helps. Do let us know if you run into any issues on that, want to make sure you’re all set here! 

Userlevel 1
Badge

Hi,

This is really helpful. However, it isn’t always the first 2 letters. It’s just upto the first number.

I did look at using the split function but I couldn’t figure out how to split at the number (since the number changes everytime)

Userlevel 1
Badge

And also, if it can capitalize the output at the same time, that would be ideal

Userlevel 7
Badge +11

Ah, I see. Thanks for clarifying that @otimo!

Yeh Split Text won’t work for this sort of use case. It’s better for instances where there’s a dividing character like a space or comma etc. It’s possible to use that Formatter (Text > Extract Pattern) action to extract the first set of letters, regardless of however many there are, you’d just need to use a different regex pattern.

I’d suggest trying this pattern instead: ^(?i)[a-z]+(?=\d)
b3e27ced5da1bed2c96005556a0d68c2.png
That pattern should be able to extract the first set of letters. Regardless of how many there are or whether they are lower or upper case.

As the action is designed to extract patterns I don’t think it’s going to be possible to convert it to uppercase. In order to do that you’d want to use a Formatter (Text > Capitalize) action.

Please do keep us in the loop on how you get on with this! 🙂

Userlevel 1
Badge

Hi This is great! Thank you so much