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!


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