Pulling Data from string

[Brandon_Reyes]

Hello,

Currently, we have payment data that comes in from Wordpress via a .csv through Zapier. The problem is it comes into one column like this:

s:212:“a:6:{s:17:“bankaccountname”;s:8:“Checking”;s:19:“bankaccountnumber”;s:9:“111111111”;s:9:“bankname”;s:5:“BigBank”;s:19:“bankroutingnumber”;s:9:“000000000”;s:9:“bankiban”;s:0:”";s:14:“bankbicswift”;s:0:"";}";

We need to pull that data into 4 different columns - Account Name, Bank Name, Routing, Account Number.

Givens: This data is presented as a popup inside wordpress. The end goal is to automate payment requests. Currently, we would have to manually enter in these details inside our payment gateway to issue payment. With a bit of automation it would streamline the process.

Any help would be greatly appreciated!

[pcbowers]

@Brandon_Reyes As long as you are able to get that information into 1 column, you can use REGEX() functions in a formula to extract this information into individual columns. All you need to do is create 4 additional columns, 1 with each formula to extract Account Name, Bank Name, Routing, and Account respectively.

Here is an example based on the data you provided and what I am assuming would be the Bank Account Name:

REGEX_EXTRACT({Zapier Data}, 's:17:“(.*?)”')

Here is a screenshot of what it would look like:

You may have to tweak the regular expression if the format is slightly different, but as long as there is something to search for around it to distinguish each part you want to extract (i.e. s:17: or s:14:), REGEX() functions should solve your extraction issue.

[Brandon_Reyes]

@pcbowers I appreciate it brother! Works like a charm; you are a Godsend!

Thank you!