Extract Text Between Two Characters in Excel
HTML-код
- Опубликовано: 12 апр 2014
- Extract text between two characters in a cell. This video looks at how to extract text between two forward slashes (/) in Excel. It will not be straightforward and multiple functions are used together to achieve it.
The LEFT function is used to extract a specified number of characters from the start of text. And the MID function is used to pass the relevant text to it. FIND functions are used to locate the two characters for the formula.
=LEFT(MID(B3,FIND("/",B3)+1,LEN(B3)),FIND("/",MID(B3,FIND("/",B3)+1,LEN(B3)))-1)
The formula above can be adapted to your needs. The characters may be different but the process remains the same.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 
Хобби
Thanks for putting the formula in the description, you're a good man!
You're welcome Richie. Thank you.
Great example of something we all need to do from time to time that is extremely difficult to explain. Thanks for taking the time to put this together and share.
This is exactly what I needed. Thank you for this!
No worries!
This guy is LEGENDARY! Thank you so much!
Thank you my friend. Have a smashing day.
Perfect, I was trying to extract the domain from a list of email address, this worked wonderfully!
Alan - I have been a fan of yours for several years now and truly appreciate your Excel expertise. This video about extracting text between two characters has been just want I need for my first project in my new job. Thank you so very much! I am sure this formula can be modified if you encounter a text string in a cell where you have to extract some text that falls between two different characters (e.g. a "/" and a "_" ). Thanks a million again!
You're welcome, Douglas. Thank you. Yes, these formulas can be adjusted as needed. Power Query and Flash Fill are alternatives for this kind of work nowadays also.
Very helpful explanation! I've honestly never seen a better walk through for an Excel formula. Just helped me solve an issue with a project I've been working on! Thank you!!
You're very welcome Jake. Thank you.
THANK YOU SO MUCH it took me over an hour to find this. You are amazing.
No problem Dion. Happy to help.
Great tutorial! This helped me tremendously.
Perfect: no mumbo-jumbo, no what the hell? You said it and showed it and I did. Beauty!! Cheers, Mark
Great explanation...Just what I was looking for :)
Thank you so much! You saved me a day's worth of work!! YOU ROCK
Thanks buddy, you saved my labor of hours and hours !
I was looking for so long to find this. Good form!
Thank you Brian.
Thank you so much! I am not sure if I'll be able to create it myself in the future, but I was able to create a formula by following your instruction step by step. I'll be able to use this formula for my future entries!! It saves me a lot of times.
Excellent, thanks Lana.
Man you are a life saver, I used to extract coordinates from Google Maps URL !
Thank you. Happy to help Rehan.
Thank you so much computergaga :)
You made my day!!
Glad to hear it Wasimali, thank you.
I was looking for +2 hours, genius man thanks !
No problem Raw Smoke.
Thank you so much for this explanation! You tackled it so well!
Glad you enjoyed it! Thank you for your comments.
Absolutely fantastic - THANK U!
You are more than welcome Florian.
Thank you very much. You saved a lot of my time by this...... THANKS YOU AGAIN
Awesome! it works great. Thanks!
Keep up the good work @Computergaga ,this helped me a lot!
Great to hear David. Thank you.
incredibly helpful!!! thank you!!!
You're welcome. Thank you.
AWESOME!!! Thank you!
You're welcome, Luis.
Thank you soooo much! You literally save my life!
No problem. Glad to help.
Epic video and extraordinary Excel skills. Kudos to Computergaga.
Thank you very much, Michal.
I never comment on youtube videos but I'll do so right now just to express my sincere gratitude for this incredibly helpful video.
Francisco A Rodriguez Thanks Francisco, much appreciated.
very useful ..thank you
You are the man!
You are the best! Thank you!
You're very welcome Vlad.
You are a hero. Thank you!
Thank you Courtenay.
genius example, exactly what I was looking for. Fantastic expert!
Thank you very much, Alexandra.
Amazing Amazing..exactly what I needed
Excellent. Happy to help Jaya.
Very Awesome.....your video illiminated the massive headache that was starting. cheers =D
***** Good to hear
Thank you for great contribution!
You're welcome. Thank you Selahattin.
Subscribed!!! And the code worked for me very perfectly.
Excellent! Thank you Rabiul.
Thank you very much!! You are amazing!
Thank you 😀
That is magnificent. Thanks a lot. 👍
You're very welcome Salim.
Awesome! Thanks a million.
You're welcome!
thanks mate, i really needed this information
No problem 👍
Thank you so much for this! Just saved me hours of work!!!
You're welcome. That is great to hear.
EXACTLY WHAT I NEEDED. THANK YOU!
You're very welcome.
@@Computergaga Would you please help us with extracting between second and third delimiters? That would be very much appreciated.
thanks very helpful
Great to hear, Derrick.
YOU ARE A GOD SENT!!! THANK YOU SO MUCH
You're welcome!
thanks so much for this.. an awesome and simple explanation and hopefully will help me figure out how to expand this to work with more characters with text between them (say 4 or 5 "/")
Mark Cunningham Computergaga I am really need help with this. It would really mean a lot if you could help us out with this.
Holy damn! Thank you!!!!!!
No worries, Andy.
Thank you! It helped me too...
Great to hear. Happy to help.
Thank you so much
You're welcome Omar.
Thanks man this is 2018 and your video helped me a lot
Great to hear.
*2019
Awesome... thank you
You're very welcome Adarshram.
you are god to me!!! thank you. Your like Monster Kill ...beautifull function it is 200% what i needed
Great to hear, thank you arkd3um1988.
Thanks a lot great Explain sir
Most welcome
Great Video
Thank you Navjinder.
Thank you!!!
You're welcome Felipe.
Superb!
Thanks a lot!
You the best!
Thank you Weston.
thank you sir....
Most welcome
Brilliant helped me allot ;-)
Great to hear! Thank you, Marta.
Extremely helpful! Thank you!
*used to extract company name from e-mail, located between the @ sign and the period before .com.
Great work Cristian.
Thanks a lot, just i solve with your formula...
Brilliant! You're welcome.
AWesome video
Thanks!
AmAzInG !!!
Thank you, Rajat.
Superb thanks
My pleasure, thank you.
@@Computergaga Amzing!
You are genius!
Thank you!!!
It was kinda hard at first, after I tried several times, i had to work it myself lije you said till i got the hang if it,
This is what i did
=MID(I117,FIND(":",I117)+2,FIND(".",I117)-FIND(":",I117))
I had long statments with several of the same, had to figure a way to pull all my practicing together and cells,
I did it, Lol
Thank you much!!!
Happy to help William. Good work!
How would you find the nth occurrence of a character and extract everything between the nth and nth +1?
thank you
You're very welcome Alexander.
You’re the best
😊
Fantastic video, I want to accomplish the very thing that you are explaining but what if the reference cell column is a formula rather than a value? In my case a long single text string with hundreds of carrots throughout has been broken up and now I wish to extract data in the individual cells between the carrots. Is a copy and paste with values my only option?
Thanks Mate...
You're very welcome Uttam.
Great vedio
Thank you Mohan.
Love from India
🙂
TI AMO!
Ti amo anch'io
@@Computergaga ahahhahaha great!
what if my data string does not have space in between and i want to extract diferent set of numbers from numericals?
You Rock
Thank you.
How would you extract a string that's between the 6"/" and 7 "/", which are in the middle of a URL?
I sure could use your help. I need to extract the data after the last slash but before the period. The length is not constant. Would you use the RIGHT function instead since there are multiple "\"?
\Civil\Blocks\2D FLAT BACKGROUND.dwg
Hello Sir,
You always provide very needful videos. Thanks for this help.
Sir I have a question. Let's suppose we have a paragraph of 800 characters. we need 1 or 2 or 3 complete lines from start of the paragraph but the length of all characters is not more than 230-240 and also not less than 140- 150 characters.
Please solve this problem Sir.
In case the text to find is between the 2 words "init" and "end" (for example) which should be the right formula, please?
Other question: is it possible to create a function (and how) TextBetween(phrase,txt1,txt2) in the scripts, please?
thank you
BC_Legg_Black_XS
BC_Capri_Sangria_XL
BC_Legg_Black_XXL
I want to extract the Black, sangria from this sting what formula do I use?
Can someone share sample? I always get invalid formula, no matter what formula I try from the web. Thanks
Awesome! What about if not all the rows have a second "/"?
There would need to be some other way of identifying the of the text to extract such as a different character, space or the end of the text itself.
I have other videos on my channel demonstrating extracting postcodes, and text from the nth occurrance of a space which may help.
12345vikrant12345 how can find text and numeric diffrent diffrent pls help us from this question
THANKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
You're welcome.
Thank you very much for this video!! I have a question please, how can I separate the names if they have a minus symbol (-) in between? Example: John-Smith
Thank you so much!!
You're welcome. Thanks.
You could use Data>Text to Columns and use the minus symbol as the delimiter. Or Flash Fill.
Thank you for the tutorial. I am trying to use your formula, the problem is I have a string of text between < and > the formula works great for this, but, it finds the first pair of < and > and I need the second < and > string. Is there a way? Maybe make grab both and then I can separate the two in a second operation?
You're welcome. If you are using Excel 365 or online, you can use the following formula.
=TEXTBEFORE(TEXTAFTER(D2,"")
The TEXTAFTER function extracts all text after the second instance of a "".
@@Computergaga WOW!!! Thank you very much!!
Hey - what do you do if there is more than one / and you want want it to remove from the one nearest to the right? For Example: ABC/testing/Remove this text as well /DEF
So how do I end up with ABCDEF?
You can do this with a formula, although it would be quite intense. I have a video on getting the nearest to the right here - ruclips.net/video/ZvDETybtZZo/видео.html
This could be concatenated onto a LEFT function extracting the first 3 to get your ABCDEF.
If you have a version 2013 or later - Flash Fill or Power Query will also help.
what will be the formula for Extract Text Between Three Characters in Excel? please help. and the characters are "."
thanks so much but i need n of words in line to extract word but each line is different size for e.g customer name in between and search the customer with : can u tell me
Sorry Gayatheri, I'm not sure what you mean. Do you have an example of a line and what you need from it.
@@Computergaga how about extracting the text on a cell but instead of a sign it would be integers numbers like this example:
10065_rts0934
Like getting the "rts".
I was able to get the your formula to work by adding another "_" after the "rts" but that's not what I need
Hey this is really useful. But what if that cell has 4 "/" and I want all the text between the third and the fourth "/" ? Also, would this work on google data studio
Thank you. I have a video to answer your question here - ruclips.net/video/A6S3-I-8rRc/видео.html
I don't about Google Data studio.
Need help on constructing formula to get start date of each task of project. project start on 1st day of month then sub task are dependent on various other task date overlapping so task 2 start andend from day 4 to 6 and task 3 from day 4 to 8 now two task are starting from same day but ending on different date so need formula to get start date of task taking 1 day as base plus each task start day from base date give result
hi, I would like to do the same from the RIGHT, but it does not work by just changing LEFT to RIGHT, what am I missing? Trying since houres to do it. any help is much appreciated. many thansk! in advance....Robert
I have this video on performing a reverse FIND Robert - ruclips.net/video/ZvDETybtZZo/видео.html
Hopefully this can be adapted for your needs.
2. What text is contained between "[ ] "in Description column
That is num_chars, which stands for number of characters. The [ ] indicate that an argument is optional.
SRVKOL/236/22-23
SRVKOL/5389/22-23
SRVKOL/2/22-23
SRVKOL/58/22-23
I want to extract only numbers (i.e., 236, 5389, 2, 58) using formula. Please advise. Thanks
This saved me. I have a question and I hope you respond. I want to make this formula into a condition, so that if the specific character im looking for isn't in that text, instead of excel giving me a !VALUE (error value message), I want it to give me a 0 or nothing. So, basically: If ( you find this specific character, then move it to this column. If you don't find this specific character, put a 0).
HELPPPP PLEASE
Hi Maria, you could add an IFERROR function around the formula from the video. So you could have =IFERROR(video formula,0) to show 0 or =IFERROR(video formula,"") to show and empty cell.
Thank you!!!!
You're welcome.
I need something like this to extra the weather from this line:
Fair
So it would give fair... or Snowy... or Cloudy, or shower. Is there any way you can help me?
One part down, more to go lol I managed to get that part done fooling around with functions
=MID($A20,SEARCH(">",$A20)+1,SEARCH("",$A20)-SEARCH(">",$A20)-1)
This works exactly like I need it to, I even replaced the different weathers with random words to make sure it would work correctly. Now I just have the long arduous task of figuring out which ones act as in game growth ticks this way so if the weather is ever changed, I don't have to change the whole spreadsheet
Excellent work. You could change the text in the second search to "
@@Computergaga Well see its a script code from a game for the weather patterns. With this I need to be able to add any code the server might be running. I also need to figure out a way to account for when the growth periods are (from Fair to Shower / Shower to Fair) from one day to another. And somehow, I have to do this while ignoring cloudy
Fair
Snowy
Cloudy
Shower
Shower
Shower
Cloudy
Shower
Shower
Shower
Fair
So in this, there is a growth tick at day id="57" and again at day 64.
Can you please let me know how extract a abbreviated word from a single sentence. For eample if I have a sentence " COW EATS GRASS" in a single cell and I want to extract onlt the initial letters of the all three words from the sentence "CEG" in one single cell, then how can I do that. Please help. Thank you....
The formula would be quite intense for this. Especially if we do not know how many words in a cell. The easiest bet is probably the Flash Fill tool, Power Query or a custom function in VBA.
hey, can you provide me the formula to find "Karun Kumar" from the string "new report Karun Kumar. The credentials are below:" Tried the above formula unable to get it right
Hi Kedar, I have this video on creating a reverse FIND formula which will do the job. You will need to find the second space in your exampe as the delimiter for the beginning of Karun Kumar. This can be done with the SUBSTITUTE function.
@@Computergaga Please upload video by using Substitute function. I also need to extract from string the name of person written in all caps (only name in all caps).
Hello I tried this formula for this "#Opportunities=23#Defects=34
" by twinkling the cell value...its not working i need to extract only the numbers like 23 and 34
If the numbers are always 2 digits you can use =RIGHT(A2,2) and if you need it formatted as a number =VALUE(RIGHT(A2,2))
If you do not know how many digits there are you could use =RIGHT(A2,LEN(A2)-FIND("#",A2))
Clever. I have a similar problem with a load of product codes, separated by + signs, but I need to extract the string after the first +, need to include the next + then rest of text before the final 3 +'s, eg: Mo+Sil+A-174+22+50+128, I need to end up with Sil+A-174. To+42-154a+180+22+250 I need to end up with 42-154a. Any hints?
If the value is in cell A1, then the formula below will work.
=RIGHT(LEFT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"+","*",2))),LEN(LEFT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"+","*",2))))-FIND("+",A1))
You can separate large formulas like this into separate helper columns to break them up if easier to manipulate.
It combines a LEFT function to extract everything up the the third + from the end, and then a RIGHT function to take after the first +.
Have you tried Flash Fill? That may help also.
Sorry I just noticed you had 5 +'2 in the second one.
In cell B1 you could use this formula - =LEFT(A1,FIND("*",SUBSTITUTE(A1,"+","*",LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))-2))-1)
Then this one uses the result in B1 - =RIGHT(B1,LEN(B1)-FIND("+",B1))
This will work for both examples you demonstrated.
Thats great, many thanks, the second two-stage option returns most consistent results and is a little easier for me to get my head round :)
what is the purpose of the *?
That is used as a marker in the SUBSTITUTE function to identify the position of the + 3 from the end. It is then used in the FIND function to find the end of the string for LEFT to extract.
hi, i wanted to see if you can help me. if i have a number that i want to remove a character how would you do it. i want to remove "/" from this text DL1C/152358
SideBoard 39 You can use the SUBSTITUTE function and use it to replace the "/" with "".