CODEWITHSUNDEEP
google-sheets
AlfredRomero
AlfredRomero

Reputation: 1

Extract some characters before specific symbol in Google Sheets

I have an email address, for example [email protected] and I want to extract first 3 characters and 3 characters before symbol @, for example I should get hel123. Second exampe: [email protected] - the result should be: m2aa55

The first part is easy - =CONCATENATE(LEFT(A1;3)) - I get first 3 characters. Picture

The second part - I can get all the characters before the symbol @ - =LEFT(A1; FIND("@";A1)-1) Picture2

But I cannot combine them or modify just to get first 3 characters and 3 characters before symbol @.

Upvotes: 0

Views: 331

Answers (2)

MattKing
MattKing

Reputation: 7773

what should happen if it's shorter than 6 characters? This works but errors out if the initial is less than 6 chars.

=REPLACE(SPLIT(A1,"@"),4,FIND("@",A1)-7,)

Upvotes: 0

Aresvik
Aresvik

Reputation: 4620

Try:

=index(if(A1:A<>"",regexreplace(A1:A,"(...).*(...)@.*","$1$2"),))

Upvotes: 1

Related Questions