Split Text at a Colon Into Two Separate Columns Using a Formula

What we are looking to do is; split the Name Column (first column on the far left of the board), but split the first section of the title at a colon “:” which would be an internal reference form number (Example “CF-26-59000-2110: Bank Product Flyer”) and then create a second column to house the second half of the name which would be the actual title of the piece. @JCorrell helped us out on a formula last time and was curious if he had any insight on this one as well? Thank you in advanced!

@vinceknueven

Here you go Vince…

This formula will give the portion left of the semicolon:

LEFT({Name}, SEARCH(";", {Name})-1)

This formula will give the portion right of the semicolon:

MID({Name}, SEARCH(";", {Name})+1,9999)

Both formulas will return an error if there is no semicolon. If you would like a “nicer” error in that case, use these:

IF(SEARCH(";", {Name}&";")<LEN({Name}), LEFT({Name}, SEARCH(";", {Name}&";")-1), "Semicolon is missing in Name.")
IF(SEARCH(";", {Name}&";")<LEN({Name}), MID({Name}, SEARCH(";", {Name}&";")+1,9999), "Semicolon is missing in Name.")

… Also, if you might have a space after the semicolon and don’t want it in the result, you can add TRIM(). Like this:

TRIM(MID({Name}, SEARCH(";", {Name})+1,9999))

Jim - The Monday Man channel
Signup for a FREE Integromat account with this link and get a FREE hour with The Monday Man