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?
Best answer by SamB
View original
Error: Failed to create a text transform in Formatter
- Beginner
- 6 replies
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.
Userlevel 7
+11
- Community Manager
- 4785 replies
-
9 February 2023
Hey there
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:
That would output the first 2 characters into a separate field that you could then select in any subsequent actions. For example:
Hope that helps. Do let us know if you run into any issues on that, want to make sure you’re all set here!
- Beginner
- 6 replies
-
9 February 2023
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)
- Beginner
- 6 replies
-
9 February 2023
And also, if it can capitalize the output at the same time, that would be ideal
Userlevel 7
+11
- Community Manager
- 4785 replies
-
9 February 2023
- Answer
Ah, I see. Thanks for clarifying that
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)
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! 🙂
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.