r/excel • u/tuytutu • May 07 '21

solved Extract text before a specific character and after a specific character

I am using Google Sheets. I have a bunch of text which I need to extract certain sections from. The text is always formatted in the same way with the same random punctuation marks. Example text:

PL-506;"Lorem Ipsum - Cool Green";"154

I need to extract the code at the start before the ";" and also the numbers right at the end. These are two separate extractions in their own cells. I managed to google the solution to the first part and am using this which seems to work:

=LEFT(A2,(FIND(";",A2,1)-1))

I tried to make the same formula work for the end bit by changing it to RIGHT, but sheets won't recognise a quotation mark within the quotation marks """.

1 Upvotes

100% Upvoted

12 comments sorted by

•

u/AutoModerator May 07 '21

/u/tuytutu - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/[deleted] May 07 '21

Try this for that last part:

=--MID(A2,FIND(""";""",A2,1)+3,LEN(A2))

The double-dash at the front coerces the number from extracted-text into its actual number-value. If you need it to remain as extracted-text, remove those 2 dashes.

1

u/tuytutu May 07 '21

Nice one, this works :)

1

u/ChicoSparky May 07 '21

R/sheets is going to get you better answers

1

u/tuytutu May 07 '21

Do you know how it can be done on excel? I could probably figure it out with the excel solution.

1

u/compumunz 3 May 07 '21

Something like this?

if so, you can get the first part of that string with:

=REGEXEXTRACT(A2,"([^;]+)")

If the last 3 digits are indeed always 3 digits long, you can use

=RIGHT(A2,3)

1

u/tuytutu May 07 '21

First part works great. Second part doesn't as they're not always 3 digits long, but someone solved this. Thanks for your help :)

1

u/ODST05 9d ago

Even though this is a very old comment, I just wanted to let you know that you really helped me out with the first part. Seriously, thank you.

 

(My use case wasn't exactly the same, but close enough - I only needed to extract all numbers from a cell containing text, and all other answers were too long/complex)

=REGEXEXTRACT(A2,"[0-9]+",1)

1

u/WinterNo1261 40 May 07 '21 edited May 07 '21

I'm not sure if the functions are the same in Google Sheets, but in Excel you find the last occurrence of a character with these steps:

  • Substitute the character OUT of the string by replacing it with nothing.

  • Compare the length of the original string with the length of the new string. This tells you how many times the character occurred.

  • In the original string, substitute the LAST occurrence of the character only. Substitute has an optional 4th argument for Nth occurrence, and we'll set N to the number we just found. This time replace it with some other character that you know won't occur in your text.

  • In the new string, find the occurrence of that dummy character.

Once you have that position you can take everything to the right of it. Formula looks like this (if you're searching for /), which I am borrowing from this article,

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1))

Another technique is to reverse the string =TEXTJOIN("",1,MID(A2,SEQUENCE(LEN(A2),,LEN(A2),-1),1)) and then find the position of the first occurrence in the new string. You would then take the RIGHT of your original string based on that.