CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Roggie
Roggie

Reputation: 1217

Extract substring after '-' character in Google Sheets

I am using the following formula to extract the substring venue01 from column C, the problem is that when value string in column C is shorter it only extracts the value 1 I need it to extract anything straight after the - (dash) no matter the length of the value text in column c

={"VenueID";ARRAYFORMULA(IF(ISBLANK(A2:A),"",RIGHT(C2:C,SEARCH("-",C2:C)-21)))}

enter image description here

Upvotes: 31

Views: 107685

Answers (3)

Mohammed Joraid
Mohammed Joraid

Reputation: 6480

Adding to your original formula. I think if you'd use RIGHT and inside it reverse the order of the string with ARRAY then that may work.

=Right(A1,FIND("-",JOIN("",ARRAYFORMULA(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))))-1)

enter image description here

  • It takes string from the right side up to X number of characters.

  • Number of character is fetched from reversing the text, then finding the dash "-".

  • It adds one more +1 of the text as it will take out so it accounts
    for the dash itself, if no +1 is added, it will show the dash on the extracted string.

The REGEX on the other answer works great too, however, you can control a number of character to over or under trim. E.g. if there is a space after the dash and you would like to always account for one more char.

Upvotes: 1

Rousseau Nutter
Rousseau Nutter

Reputation: 81

To answer bomberjackets question in the comment of Raserhin:

To select the part of the string before the "-"

=REGEXEXTRACT(A1,"(.*)-.*")

EXAMPLE

example of code

Upvotes: 8

Raserhin
Raserhin

Reputation: 2676

There is a much simpler solution using regular expressions.

=REGEXEXTRACT(A1,".*-(.*)")

In case you are no familiar with Regular Expressions what this means is, get me every string of characters ((.*)) after a dash (-).

Example

Sample of RegExtract

Reference

Upvotes: 67

Related Questions