CODEWITHSUNDEEP
google-sheets
Ali Camarata
Ali Camarata

Reputation: 147

Parsing data in Google Sheets

I have customer data from PayPal formatted like this for their recurring payments:

john doe: [email protected] ID: I-1ABCDE123ABCDescription: ABC 123

I want to parse this in a column to get just the email address.

Upvotes: 0

Views: 4263

Answers (3)

Marc
Marc

Reputation: 1

You could use FIND to get the position number of the @ in the string, and then also use FIND to get the position number of the first blank space before and after the position of the @. Now let's say the @ is in position 26 of the cell, and from position 26, the next blank space in the cell going right is 37, and the next blank space going left from position 26 is at position 15, then you'll know that your email address starts at position 16 to 36. Now all you have to do is use the MID formula from there to get your email address.

Also, I just noticed you dont have a space at the end of the email address... if the word Customer ID always follows the email address, then instead of finding the next space to the right of the @, you could use the FIND to get the position number of this word instead, and do the same.

Upvotes: 0

U. Andrian
U. Andrian

Reputation: 26

I know it's a bad example, but it work (use explode function in php)

$string = 'john doe: [email protected] ID: I 1ABCDE123ABCDescription: ABC 123';

$array = explode(" ", $string);

echo $array[2];

Result: [email protected]

The better it will be if you will use the Regular expression

Upvotes: 0

Ali Camarata
Ali Camarata

Reputation: 147

Forgot about index which simplified the approach a lot. My only thought is the possibility of the name being 1 or 3 words instead of 2 (First and Last) which would throw off the index. You could use if statements to detect the @ symbol, then substitute (to remove the non-email part) and return the result but the simple version below worked on all rows of my data:

=substitute(index(split(B2, " "),3),"Customer","")

Upvotes: 1

Related Questions