Highline Excel 2013 Class Video 19: Text Functions RIGHT, LEFT, MID, SEARCH for Partial Text VLOOKUP

[@7788897776]

I have been trained by too many, but I never found them as useful as you. You are the best

[@excelisfun]

I am glad that the videos help!

[@akinyemimcgeorge7267]

Thank you for the Teaching. In the workbook, you added "extract after two dash" which was not covered in the tutorial. When i looked at the edited formular, it contains a function you did not cover in this class. The "Subsitute" function. I cant wait to get to that section of the class. Many Thanks.

[@excelisfun]

Maybe one option. If you had: Bellen-234-B25R-77TTS in cell A1 and you wanted what is after 2nd dash and before third, then maybe: =MID(A1,SEARCH("#",SUBSTITUTE(A1,"-","#",2))+1,4) This would get: "B25R" Here is a video title: Excel Magic Trick 559: Extract Nth Word In Text String

[@Pablo513358]

You are the best trainer on Excel so far! Been learning from you since 2013. Thank you!

[@excelisfun]

You are welcome! I am glad I can help you : ) You can help me too : ) By clicking that thumbs up and leaving a small comment on each vid : )

[@alexnapier111]

AMAZING, you have inspired me so much. I can't stop watch your videos they are so insightful, clear and a great help.

[@excelisfun]

Glad the videos help!

[@joshuamanampiu6489]

I put this to practical application today changing a date in word to an excel format. It is a really nice feeling to extract the number and then create a date. Thanks so much.

[@excelisfun]

Cool! I am glad that the video helps!

[@excelisfun]

That is always good advise!

[@MrElectricwave]

Very useful video, Mike. Thanks for teaching us as always.

[@veereshkummari3196]

Thankyou ❤ I can understand..

[@noxtorious2962]

FINALLY!!!

[@veereshkummari3196]

Partial text lookup: right, table formula in why are you using 4.. ?

[@michaelgowlett5036]

Hi Mike on rows 21-24 at vid time 11:12 you mention about having a pattern, what happens if you don't have a pattern? I.e. I tried to use the same formula to extract the surname from 2 email add that don't have a numerical or a text pattern similarity. In other words using your example you used a number 3 because you have 3 digits always, I don't have that option.

[@kingfighter08]

Thanks Sir

[@excelisfun]

You are welcome!

[@trinadang8956]

Amazing !! thanks so lot,

[@alonariel8170]

Hi Mike, for every video i watching,and to practice it, all the homework sheet in the file are relevant for the subject? thanks in advanced.

[@thehitman781]

I'm trying to do the following using the search function to return me the text string not the value or number. For example I have colum name model there is where the formula is =SEARCH(C7;C11:C18;D11:18) but is promting me the error #VALUE! C7 =code that I want to look for this are number C11:C18= Are the list codes. D11:D18= Are the models those are string value or text. So for you enter understand I input in cell C7 the value"1" it should compare it in the list that are in C11:C18 doing this comparation it should compare what is D11:D18. Example ref code = 1 should be the model = dell I want the search to return me dell as the value in the search. both colums are in the general format.

[@vijaysahal4556]

nice 🔥🔥🔥

[@excelisfun]

Partial Text VLOOKUP : ) : ) : )

[@stevennye5075]

very nice

[@excelisfun]

Glad it works for you, Steven Nye : )

[@XLarium]

Hello Renzo Liso Better ask your question in an Excel forum; like mrexcel.com. In a forum you have better ways to "illustrate" your problem.

[@rbgsound5129]

Hello, I have a 2 page workbook where my goal is to give a user the ability to enter a 6 digit alphanumeric code into a single cell, e.g. A1 that will automatically populate 6 different cells, thus activating all my lookups. I'm attempting to use LEFT, MID and Right functions to populate the 6 separate cells with info from A1. A2 will show character 1 only from A1. A3 will show character 2 only from A1. A4 will show character 3 only etc.... Each alphanumeric digit will be looking up a separate part of a code from a large chart using VLOOKUP. Everything works without the LEFT, MID, or RIGHT functions. All cells have the potential of being a number or text. My question is; How do I get the cells to recognize either a text or a number and show them as such, so that, when I do a VLOOKUP, it recognizes both numbers and text from the chosen array? Thank you. V/R, Rich

[@excelisfun]

Try: mrexcel.com/forum

[@millerhomr2103]

Shit I am the only one watching 2 years ago video ? ?this is sooo good

[@shailrock5173]

I would help to use in woek

[@NguyenNguyen-nr8bx]

I need to hug you >:D< just what I am looking for.