How do you extract characters from a string?

Question

I want to send emails from my google sheet. I want the emails to address recipients by their first name or the name they are known by. The following is an example of my data in column A.

I need a formula in column B that extracts the first name from column A but if they are known by a different name, the name in the brackets in column A, then I want that name extracted to column B instead. I have the following formula which works for names in brackets but I need help with the formula to extract the first name when there are no brackets.

=(SPLIT(REGEXEXTRACT(A2,"\((.*)\)"),","))

player0 · Accepted Answer · 2019-11-07 23:18:22Z

1

one-cell solution:

=ARRAYFORMULA(IFNA(IFNA(
 REGEXEXTRACT(A2:A, "\((.*)\)"), 
 REGEXEXTRACT(A2:A, ", (.*)"))))

answered Nov 7, 2019 at 23:18

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Shulman · Accepted Answer · 2019-11-07 03:37:52Z

1

Try this. In B2, enter this formula:

=iferror(SPLIT(REGEXEXTRACT(A2,"\((.*)\)"),","), mid(A2, find(", ", A2)+2, len(A2)))

Explanation:

The first part is yours: SPLIT(REGEXEXTRACT(A2,"((.*))"),",")

As you've seen, this returns an #ERROR if the "(" isn't found. So use iferror to wrap that. The second part is returned if there is an error: =mid(A2, find(", ", A2)+2, len(A2))

The mid() function returns a substring from a string. The first argument is the string that you're looking in, found in A2. Then, The starting position of the substring is the location of ", " (offset by 2), and continues to the end of the string.

answered Nov 7, 2019 at 3:37

Michael Shulman

6515 silver badges6 bronze badges

2 Comments

McChief Over a year ago

Perfect - thank you for your help and the explanation.

CodeCamper Over a year ago

@McChief this works but you can also use index(split(A2:A," "),,2) see my answer for how to do it with an arrayformula so you do not have to drag down.

CodeCamper · Accepted Answer · 2019-11-07 04:47:19Z

0

Try this. In B1, enter this formula:

={"Salutation";arrayformula(if(A2:A="","",iferror((SPLIT(REGEXEXTRACT(A2:A,"\((.*)\)"),",")),index(split(A2:A," "),,2))))}

Explanation:

The first part is yours: SPLIT(REGEXEXTRACT(A2,"((.*))"),",")

As you've seen, this returns an #ERROR if the "(" isn't found. So use iferror to wrap that. The second part is returned if there is an error: index(split(A2:A," "),,2)

The index() function returns the second column from the split(). The advantage to doing it this way is you will not have to drag formulas down.

answered Nov 7, 2019 at 4:47

CodeCamper

7,0148 gold badges49 silver badges101 bronze badges

8 Comments

McChief Over a year ago

thank you. In my actual spreadsheet column G is the Full Name and column I is the Salutation. I changed all of your code from A2:A to G3:G but I keep getting an error.

CodeCamper Over a year ago

@McChief you have your headers on the second row or the first row? If you have your headers on the first row it should be G2:G

McChief Over a year ago

the headers are on the 3rd row

McChief Over a year ago

I changed the last 2 in your code to 3 but it still doesn't work

CodeCamper Over a year ago

@McChief if the header is on the third row you need to change it to G4:G

|

Collectives™ on Stack Overflow

How do you extract characters from a string?

3 Answers 3

Comments

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related