Skip to main content

Hi Airtable folks,

 

I’ve got this really fun bookkeeping base I’ve created, which I use in conjunction with Fintable.io (a way to push bank transactions into Airtable – highly recommend!). Since Fintable downloads the transaction names from my bank statements in all sorts of different ways depending on how the banks format them, I wanted to create a formula to extract the Vendor name from when I receive or send money through Zelle. The thing is that this can show up in a variety of ways:

ZELLE FROM FIRSTNAME LASTNAME ON DATE

ZELLE TO FIRSTNAME LASTNAME ON DATE

Zelle Payment to FirstName LastName

Zelle Payment from FirstName LastName

Zelle Payment to FirstName

Zelle Payment from FirstName

And so on… 

 

What I’m getting stuck on is how to combine two formulas I ended up creating: one that returns both the first name and last name of the person (but when there is no last name, returns some of the random extra characters after the first name), and one that returns only the first name, whether there is a last name or not present in the transaction name. For the life of me I couldn’t find a way to nest these two together! So I ended up creating two separate formula fields, and a third one that checks when one of the two contains numerals (meaning that record has a first name only, and that formula accidentally grabbed extra characters after the name), so it can return the results from the first-name-only formula. It works, but it feels very roundabout and inefficient. Anyone willing to help me put them together? Full disclosure: I started these on my own but used a bit of AI help to develop them, which is why some aspects of them work but I can’t quite decipher them on my own to solve my issue. Formulas below:

 

Zelle Formula 1:

 

IF(FIND("ZELLE FROM", {*Name}&""), MID({*Name},12,(FIND(" ON", {*Name})-12)),

IF(FIND("ZELLE TO", {*Name}&""), MID({*Name},9,(FIND(" ON", {*Name})-9)),


 

IF(FIND("Zelle payment to", {*Name}&""),

TRIM(LEFT(MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name})),

FIND(" ", MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name})) & " ", FIND(" ", MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name}))) + 1) - 1)),

 

IF(FIND("Zelle payment to", {*Name}&""),

IF(FIND(" ", MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name}))) & " ",

TRIM(LEFT(MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name})),

FIND(" ", MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name})) & " ") - 1)),

TRIM(MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

FIND(" ", {*Name} & " ", FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to ")) - (FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "))))),

 

IF(FIND("ZELLE FROM", {*Name}&""), MID({*Name},12,(FIND(" ON", {*Name})-12)),

IF(FIND("Zelle payment from", {*Name}&""),

TRIM(LEFT(MID({*Name},

FIND("Zelle payment from", {*Name}) + LEN("Zelle payment from "),

LEN({*Name})),

FIND(" ", MID({*Name},

FIND("Zelle payment from", {*Name}) + LEN("Zelle payment from "),

LEN({*Name})) & " ", FIND(" ", MID({*Name},

FIND("Zelle payment from", {*Name}) + LEN("Zelle payment from "),

LEN({*Name}))) + 1) - 1)),

 

IF(FIND("Zelle", {*Name}&""),

TRIM(LEFT(MID({*Name},

FIND("Zelle", {*Name}) + LEN("Zelle "),

LEN({*Name})),

FIND(" ", MID({*Name},

FIND("Zelle", {*Name}) + LEN("Zelle "),

LEN({*Name})) & " ", FIND(" ", MID({*Name},

FIND("Zelle", {*Name}) + LEN("Zelle "),

LEN({*Name}))) + 1) - 1)),

 

IF(FIND("ZELLE", {*Name}&""),

TRIM(LEFT(MID({*Name},

FIND("ZELLE", {*Name}) + LEN("ZELLE "),

LEN({*Name})),

FIND(" ", MID({*Name},

FIND("ZELLE", {*Name}) + LEN("ZELLE "),

LEN({*Name})) & " ", FIND(" ", MID({*Name},

FIND("ZELLE", {*Name}) + LEN("ZELLE "),

LEN({*Name}))) + 1) - 1))

 

))))))))

 

 

Zelle Formula 2:

 

IF(FIND("Zelle payment to", {*Name}&""),

IF(FIND(" ", MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name}))) & " ",

TRIM(LEFT(MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name})),

FIND(" ", MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name})) & " ") - 1)),

TRIM(MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

FIND(" ", {*Name} & " ", FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to ")) - (FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "))))),

 

IF(FIND("ZELLE FROM", {*Name}&""), MID({*Name},12,(FIND(" ON", {*Name})-12)),

IF(FIND("Zelle payment from", {*Name}&""),

TRIM(LEFT(MID({*Name},

FIND("Zelle payment from", {*Name}) + LEN("Zelle payment from "),

LEN({*Name})),

FIND(" ", MID({*Name},

FIND("Zelle payment from", {*Name}) + LEN("Zelle payment from "),

LEN({*Name})) & " ", FIND(" ", MID({*Name},

FIND("Zelle payment from", {*Name}) + LEN("Zelle payment from "),

LEN({*Name}))) + 1) - 1)),

 

IF(FIND("Zelle payment to", {*Name}&""),

TRIM(LEFT(MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name})),

FIND(" ", MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name})) & " ", FIND(" ", MID({*Name},

FIND("Zelle payment to", {*Name}) + LEN("Zelle payment to "),

LEN({*Name}))) + 1) - 1)),

 

IF(FIND("Zelle", {*Name}&""),

TRIM(LEFT(MID({*Name},

FIND("Zelle", {*Name}) + LEN("Zelle "),

LEN({*Name})),

FIND(" ", MID({*Name},

FIND("Zelle", {*Name}) + LEN("Zelle "),

LEN({*Name})) & " ", FIND(" ", MID({*Name},

FIND("Zelle", {*Name}) + LEN("Zelle "),

LEN({*Name}))) + 1) - 1)),

 

IF(FIND("ZELLE", {*Name}&""),

TRIM(LEFT(MID({*Name},

FIND("ZELLE", {*Name}) + LEN("ZELLE "),

LEN({*Name})),

FIND(" ", MID({*Name},

FIND("ZELLE", {*Name}) + LEN("ZELLE "),

LEN({*Name})) & " ", FIND(" ", MID({*Name},

FIND("ZELLE", {*Name}) + LEN("ZELLE "),

LEN({*Name}))) + 1) - 1))

 

))))))

 

Thanks for the help!

Someone smarter than I is going to come in with the proper formula solution for this, but I think this is a REGEX_EXTRACT() situation. You can use REGEX to pull the third and fourth words, though you might need two versions nested in an IF() to account for “Zelle from” and “Zelle Payment from”, so in the first instance you pull words 3 and 4, or in the second words 4 and 5. 

Hm, not entirely sure what the desired output is, does this look right?

TRIM(
  REGEX_EXTRACT(
    {Name},
    "(?i)zelle (?:payment )?(?:from|to)\\s+([a-z]+(?:\\s[a-z]+)?)"
  )
)

 

Thanks so much for the responses! ​@TheTimeSavingCo, yes, that’s what I was looking for! Your formula worked in most instances, but I ran into a couple of hiccups: 

  1. it’s not often, but sometimes the vendor name is three+ words long (example: Zelle payment from PRIME NEW YORK, LLC), and in such cases only the two names show up (“PRIME NEW” in this case). 
  2. I found another weird instance where things are formatted like this: “TD ZELLE RECEIVED 50170G09ABXC Zelle BENNY JAMES” and I assume because it says Zelle twice, the formula throws an error.
  3. It also throws an error when the formatting is: “Zelle Jaylen Dobbs“ (FYI these names are all made up, don’t worry!)

I guess I hadn’t identified EVERY SINGLE way Zelle transactions can possibly be formatted, though I did try to think of most options.

Also, I’m wondering if there’s a way to include in the formula a standardized capitalization for the results (I’d love to have the first letter of each word uppercase, and the rest lowercase if possible)?

 

You’ve already helped so much just with what you gave me, so no pressure to work on this any more, I really appreciate the input you’ve given. Just thought I’d throw it out there in case anyone wants to trty, and if it’s helpful for other folks.

Thanks,

Anne

No worries!  How does this look?  

The LLC isn’t formatted properly, but I figure you can fix those by putting a ‘SUBSTITUTE’ on top.  It’ll all have to be hardcoded, but there shouldn’t be that many of these I reckon

 

I found the formula for capitalizing the first letter of each word here (standing on the shoulders of giants etc heh)

 

SUBSTITUTE(
  REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(

TRIM(
  REGEX_EXTRACT(
    LOWER({Name}),
    "(?:zelle.*?zelle|zelle(?: payment)?(?: from| to)?)\\s+(.+?)(?:\\s+on\\s|$)"
  )
) , 

  "^a", "A"), "^b", "B"), "^c", "C"), "^d", "D"), "^e", "E"), "^f", "F"), "^g", "G"), "^h", "H"), "^i", "I"), "^j", "J"), "^k", "K"), "^l", "L"), "^m", "M"), "^n", "N"), "^o", "O"), "^p", "P"), "^q", "Q"), "^r", "R"), "^s", "S"), "^t", "T"), "^u", "U"), "^v", "V"), "^w", "W"), "^x", "X"), "^y", "Y"), "^z", "Z"), " a", " A"), " b", " B"), " c", " C"), " d", " D"), " e", " E"), " f", " F"), " g", " G"), " h", " H"), " i", " I"), " j", " J"), " k", " K"), " l", " L"), " m", " M"), " n", " N"), " o", " O"), " p", " P"), " q", " Q"), " r", " R"), " s", " S"), " t", " T"), " u", " U"), " v", " V"), " w", " W"), " x", " X"), " y", " Y"), " z", " Z"),
  ', Llc',
  ', LLC'
)

 

 

Thank you, ​@TheTimeSavingCo for taking another stab at this! What’s happening now is that across the board whenever there is anything following the names (usually a bunch of random characters) those get added in too… eg”Zelle payment from PRIME NEW YORK, LLC 21061804251” becomes “Prime New York, LLC 21061804251” and “Zelle Payment to Pete Wilson 23648791677” becomes “Pete Wilson 23648791677”. Is there a way to dynamically filter that out? That was sorta the original issue I set out to fix, and I hadn’t known a way to clip those other than using LEN( and the left/right functions, but it was clunky. I don’t really know much about Regex functions so I’m a bit lost here…

Honestly, most folks in my use cases have first name and last name only, so if I had to cap it at two words I’d be ok (or maybe there’s a way to take all words before an “,LLC” or something like that, which would take care of longer company names?). I was just trying to avoid the random characters that come along when the system is set up to grab first and last name, and there happens to only be a first name, which happens sometimes.

 

Thanks again for the help!

Hmm, yeah we’re going to need to identify some kind of pattern to try to filter it out.  Does the last set of characters adhere to any rules?  I made it filter out any trailing digits on the off chance it’s that simple

SUBSTITUTE(
  REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(

TRIM(
  REGEX_EXTRACT(
    LOWER({Name}),
    "(?:zelle.*?zelle|zelle(?: payment)?(?: from| to)?)\\s+(.+?)(?:\\s+\\d+|\\s+on\\s|$)"
  )
)
, 

  "^a", "A"), "^b", "B"), "^c", "C"), "^d", "D"), "^e", "E"), "^f", "F"), "^g", "G"), "^h", "H"), "^i", "I"), "^j", "J"), "^k", "K"), "^l", "L"), "^m", "M"), "^n", "N"), "^o", "O"), "^p", "P"), "^q", "Q"), "^r", "R"), "^s", "S"), "^t", "T"), "^u", "U"), "^v", "V"), "^w", "W"), "^x", "X"), "^y", "Y"), "^z", "Z"), " a", " A"), " b", " B"), " c", " C"), " d", " D"), " e", " E"), " f", " F"), " g", " G"), " h", " H"), " i", " I"), " j", " J"), " k", " K"), " l", " L"), " m", " M"), " n", " N"), " o", " O"), " p", " P"), " q", " Q"), " r", " R"), " s", " S"), " t", " T"), " u", " U"), " v", " V"), " w", " W"), " x", " X"), " y", " Y"), " z", " Z"),
  ', Llc',
  ', LLC'
)

 

Thanks for continuing to work on this for me, ​@TheTimeSavingCo ! Unfortunately, the random characters at the end are not just digits, they’re a mix of digits and letters, so while most of the instances now work great, I still get stuff like “Andrew King Jpm99b41jscd” when I try your new formula. That’s the thing with trying to take info from various different banks, they each have their own way of writing stuff out, and when I think I’ve got them all, a new weird one pops up and I don’t know what to do… 

My very rudimentary solution was to add another formula field to check whether this field has any numerals, and if so, use a different formula that reliably trimmed off the second word of every name. Of course that’s not ideal, but I don’t know that I’ll actually be able to find a pattern to use for these types of transaction names where it’s only the person’s name and then a bunch of random characters.

Hmm...yeah, let me know if you can find a pattern for it and I’ll see what I can do.  Do you think we could apply rules based on the bank the data’s coming from?  So maybe for this bank we just trim out the last word?  

Reply


Terms & Conditions