Google Sheets Trim after either of characters

Question

I have a column of cells in Google Sheets with text in the following formats:

PLAYBILL59; Code Description Here
BROADWAYBOX59: Code Description Here
TICKETCODE: Code Description Here

I want to create a formula that deletes everything after and including either a colon or semi-colon, that would leave:

PLAYBILL59
BROADWAYBOX59
TICKETCODE

I've been trying for hours with no luck.

Any suggestions very appreciated.

Probably a hefty LEFT FIND LEN assembly, greatly simplified by using a few columns, unless you wish to use a macro. — ti7, Jun 29 '16 at 17:37
I tried using =IFERROR(TRIM(LEFT(V180,FIND(";",V180))),V180), but that leaves the semi-colon, and i can't work out to find **either** a colon or a semi-colon. — Kyle Johnson, Jun 29 '16 at 17:43
So i came up with this, but can't work out how to remove the colons and semi-colons =IFERROR(TRIM(LEFT(V180,FIND("; ",V180))),IFERROR(TRIM(LEFT(V180,FIND(": ",V180))))) — Kyle Johnson, Jun 29 '16 at 17:45
That edit makes a huge difference in the complexity, good stuff @D.Dimitrioglo — ti7, Jun 29 '16 at 18:15

D.Dimitrioglo · Accepted Answer · 2016-06-29T17:54:14.310

3

Let's say that your colum is A, then you can use REGEXEXTRACT in your formula like

=REGEXEXTRACT(A1; "[A-Z0-9-a-z]+")

edited Jun 29 '16 at 17:54

answered Jun 29 '16 at 17:43

D.Dimitrioglo

This work perfectly for the example I gave, but I didn't give you the full picture. Some cells have information in the format 59E59-reg or 59E59-ABC, and I need those to stay the same if there are no colons or semi-colons. Thanks so much for alerting me to this method. – Kyle Johnson Jun 29 '16 at 17:49
Is there a similar function to `RegexExtract` in Excel? That looks like a handy function. – BruceWayne Jun 29 '16 at 17:52
@BruceWayne I do not know about Excel, I'm using only google spreadsheets – D.Dimitrioglo Jun 29 '16 at 17:55
THIS IS IT! Thank you! Genius – Kyle Johnson Jun 29 '16 at 18:06
@D.Dimitrioglo - Ah, okay. FYI for anyone in the future, I found [this SO thread](http://stackoverflow.com/a/7087145/4650297) which includes a VBA macro for extracting regex in Excel. – BruceWayne Jun 29 '16 at 18:08

score 1 · Answer 2 · answered Jun 29 '16 at 17:53

1

Assuming your text string is in A1, try:

=SUBSTITUTE(SUBSTITUTE(A1, "; Code Description Here",""), ": Code Description Here", "")

answered Jun 29 '16 at 17:53

Ed Nelson

This is a very good/simple/obvious way to do what you want if you know you'll have those strings between. – ti7 Jun 29 '16 at 17:56
"and assuming `; Code Description Here` don't change" – dot.Py Jun 29 '16 at 18:00

2 Answers2