Extract Text from cells in Excel - How to get any word from a cell in Excel

[@TeachExcel]

****** BLACK FRIDAY SALE ****** VBA/Macro Course (80% OFF) -> www.teachexcel.com/vba-course-update.php?src=yt_pin_zghj4leb2dg Professional Forms in Excel (65% OFF) -> www.teachexcel.com/premium-courses/68/idiot-proof-forms-in-excel?src=yt_pin_zghj4leb2dg Email from Excel (65% OFF) -> www.teachexcel.com/premium-courses/96/send-emails-from-excel-course?src=yt_pin_zghj4leb2dg

[@sreejitakarmakar778]

You have no idea how much this helped me, i had been looking for this formula for the last 7-8 months, trying to separate the first names from huge data irrespective of the characters in the first name, and you finally explained it and it worked perfectly! Thank you so much! Genuinely appreciating this.

[@victorialee5637]

Great teaching!!! Thank you. Q: How can you extract the City (second last to the right) with a right function?

[@hojasderuta]

Give the man a hand, sooo helpful, thank you

[@Gaurav.YourDedicatedAssistant]

THANKS! =LEFT(A2,FIND(" ",A2,1+FIND(" ",A2,1+FIND(" ",A2)))-1)

[@user-di2sh1lf9h]

Thanks. This was so helpful for me.

[@srvithal38]

Million thanks .. your sharing is very helpful. You made my day.

[@NA-gn2qg]

Thanks a lot, but what i am asking is can I search for a specific string inside a larger text and copying it? I need to extract certain words and its value to use it in a research later. or delete all other words to use the search in statistics later? many thanks.

[@gangu1093]

I want to select only 2 numbers from left , please guide

[@kaivanshah4304]

amazing, it was very hard but you made it easy.

[@benwonderfulmedia]

Exactly what I needed thanks so much!

[@abhiseksingh4886]

I appreciate the logic for extradition of last word. But I found far easier and less time taking method to achieve the same without len and substitute .Totally a must watch . kzbin.info/www/bejne/fJPGiZ9qd7Vjd6s

[@JazzieLadyJ]

Very helpful and easy to follow. This is EXACTLY what I needed.

[@vedantsartcraftanddrawinga4804]

Excellent sir, thanks- shivsharan

[@carlosrestrepo2041]

what about if I only need the text after the second space?

[@abhiseksingh4886]

Find the position of 2nd space first. First position of space u can find easily. I.e. finding the position of first "space" say 5 and again in the same text find the position of "space" and keep the starting point at 5+1.

[@abhiseksingh4886]

I appreciate the logic for extradition of last word. But I found far easier and less time taking method to achieve the same without len and substitute .Totally a must watch . kzbin.info/www/bejne/fJPGiZ9qd7Vjd6s

[@psybera1987]

I want a simple Attendance register to prepare.... I can calculate the Presents marked as (P or 1) and absents marked as (A Or 0) and Overtime makes as (OT) then add salary/31+P-A+OT-fine-advance etc..... I have managed to find out the formulas for add and subtract and all the rest but extracting the OT from the P and A is giving me a touch time to figure out.

[@myleonhomes]

haha enjoying your video from Spring, TX

[@irishmumof1]

Best tutorial I have found. However, I need to go one more step further. I need to pull out just the state no space and no period. Do you have another tutorial for this? Example ("Oh")

[@SIMRANKAUR-eg7nc]

Use fixed width.I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.

[@cibulis5347]

It helped a lot. Thanks

[@prakash-uv1ct]

HI, Could you help me, how to copy particular last cell from the data

[@granand]

Thank you bro, Please may I know other types of data extraction available, can i use regex, python, pandas and VBA?

[@TeachExcel]

I have done many tutorials on this, including regex type formulas. Search my channel for them and you might find one that works well for your situation.

[@Kinleygyem55]

Thank you so much it worked 🙂

[@piplip69]

I have one question ❓ sir One person have 4 different cell numbers right how can merge all numbers in one contact number in android mobile... please make the video.

[@AwaisKhan-tk5xr]

helped a lot, Thanks

[@jgbalboa]

It's a good tutorial, but offer the "SEARCH" alternative, "FIND" is more restrictive. Nonetheless, it's a clear tutorial and gets to the point.

[@abhiseksingh4886]

I appreciate the logic for extradition of last word. But I found far easier and less time taking method to achieve the same without len and substitute .Totally a must watch . kzbin.info/www/bejne/fJPGiZ9qd7Vjd6s

[@Iam1nsane]

Absolutely what i was looking for. Thank you.

[@Matilda1804]

What function would you use if you wanted to extract just the name of the states? So everything from the right of the last space. Thank you.

[@SIMRANKAUR-eg7nc]

Right and len in combination with substitute

[@SIMRANKAUR-eg7nc]

I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video..

[@Balsero-qb9gv]

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!!

[@damians8498]

Select the cell, then go to data, select text to columns and separate by the appropriate symbol

[@payroll6408]

Thank you very much very helpful

[@RaviKumar-uw2ih]

I just love it. It reduces my effort

[@AmitChauhan004]

Saved my time.

[@abhiseksingh4886]

I appreciate the logic for extradition of last word. But I found far easier and less time taking method to achieve the same without len and substitute .Totally a must watch . kzbin.info/www/bejne/fJPGiZ9qd7Vjd6s

[@gvantsagiorgadze9352]

Hello , I used the last method to extract numbers from text, however now | struggle to sum up them as it only formats as number and can't sum up. Can you please help . Thanks

[@ericsontorralba]

hi, what if i want to use the second word only?

[@joarthcarbon9810]

Hi, how do i extract the last 3-4 words of a cell?

[@mayurkhedekar4441]

How to extract any value from the cell e.g last value.I tried this formula but it shows the start value.

[@kirandas1287]

Brilliant 👍👍👍

[@stepfanj3199]

My data is coming in this format--- "HARDER, Mr. JESSE L" ----with all the names, the "Mr. and Mrs." in my list of names is throwing my (Left, Right and Mid) formula off can you give me any insight on how to by pass this issue? It uses "Mr." as my (MID) formula. and my (RIGHT) formula does not separate the first name from the middle initial. It keeps them together with the first name. I need first/Middle/Last name in separate cells.

[@ami6737]

Thank you!! It worked!!! Woohooo

[@debid44]

Hello. Great tutorial! I have a spreadsheet with a list of times in 05:51AM or 07:13PM format and I need only the time in military format. Any suggestions?

[@SIMRANKAUR-eg7nc]

Which kind of format? Please elaborate a bit. I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.

[@LorisAyoub]

Thanks a lot. I finally got this!!!

[@abhiseksingh4886]

I appreciate the logic for extradition of last word. But I found dar easier and less time taking method to achieve the same without len and substitute ..Totally a must watch . kzbin.info/www/bejne/fJPGiZ9qd7Vjd6s

[@khailadelossantos69]

Thank you so much. ❤️

[@HeadNtheClouds]

Wow!!! You are really smart & a very good teacher!

[@mohammadghufrankhan6579]

Monitum: 28 dec 2019 16:07:52: auto RSP issue~monitum: 10 jan 2020 14:18:47 : ~vibhanshu singh bharti: 10 jan 2020 17:02:32 : monitor not working properly~ How to extract VIBHANSHU SINGH BHARTI from above column please help

[@irfananwar3447]

thank you I mange to get the number out but the issue , I cannot sum it because there is a space before the number , any suggestion

[@SIMRANKAUR-eg7nc]

Trim clean it first. Then use text to column.I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.

[@mohanpal13555]

Mind blowing sir

[@Tetchyyy]

You the real MVP

[@Sha306Sha]

Thank you🎉🎉🎉🎉🎉🎉

[@harshanathawaruna8431]

How can I do that from the RIGHT side?

[@ronyshafiq]

May i use vlookup with this extract???? Please let me know.

[@SIMRANKAUR-eg7nc]

Yea u can . I would suggest clean ur data a bit using trim clean and then proceed with vlookuo or even conditional formatting to find duplicate.

[@SIMRANKAUR-eg7nc]

I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.

[@Sandeepkol3]

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

[@ranklerave3564]

Can someone tell me if their is a function I can use that is similar to the Find & Select (binoculars) feature? I need to place a "Search for Client" box in the upper left hand corner of my spreadsheet in BIG RED LETTERS for users who have to idea how to use the normal binocular feature, which is about 90% of the users.

[@rwalis]

I found out using "FIND" in your "LEFT" or similar formulas on android or iOS does not work. Either gives me #NAME? on iOS or #Value errors on android. If anyone has a solution for these devices, I would like to know.

[@abhiseksingh4886]

Use search instead. I appreciate the logic for extradition of last word. But I found far easier and less time taking method to achieve the same without len and substitute .Totally a must watch . kzbin.info/www/bejne/fJPGiZ9qd7Vjd6s

[@ChrisKayMan]

Is there a formula to random pick a cell in excell? f.e. I have A1 to A10 cells random words, is it possible to random pick one cell from thesse cells?

[@datjusthappened101]

Fantastic video. Thank you very much!

[@papas7654]

what if you want a string from the right?

[@sachina4576]

How to do it other way around, If I want to insert a value taken form another cell into an existing string ?

[@SIMRANKAUR-eg7nc]

Concatenate

[@SIMRANKAUR-eg7nc]

I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video

[@sachina4576]

@@SIMRANKAUR-eg7nc thank you

[@mattshep6610]

how would i pull just the bridgegate dr part in this?

[@TeachExcel]

It depends... If you ask on our forum, we can give you more detailed help though. Just specify if each cell will have the same format or not and exactly what should determine how that part of the cell is put into another cell, such as using spaces as delimiters or dashes, etc. www.teachexcel.com/talk/microsoft-office

[@nextdoortechieaminfirnash6002]

The text string is "47 Nelson Street Kettering Northamptonshire NN16 8QN" in Cell A2. Here I need the street(47 Nelson Street), Town(Kettering), county(Northamptonshire) and Code(NN16 8QN) in each separate cell of a row. How to do this? Give me the formula for each retrieval. Also, another string is "Ref. No: 19/00443/EPHMO Status: Current Licence Applicant Name: Mr Jack Oliver McLoughlin" in cell A3. I need only the ref no (19/00443/EPHMO) and the name (Mr Jack Oliver McLoughlin) in separate cells. Help me with these formulas.

[@DesignCenterPEC]

What about the case, for example: A1: Alex sander 1986 A2: Jonh 1987 Kend A3: 19 Washington city Result: A1: Alex sander A2: Jonh Kend A3: Washington city ???? Please??

[@pravingaddam8541]

Have u got any answer on this

[@SIMRANKAUR-eg7nc]

I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.

[@SIMRANKAUR-eg7nc]

If there is some kind of pattern u can used fixed width

[@jay55patel]

This was very helpful. Thank you!!!

[@Akenazia]

How would i pull out just Bridgegate and Tx? or 6618 and Spring?

[@SIMRANKAUR-eg7nc]

I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.

[@furqanessaniACCA]

Nice video.. It might help me when I am given the same situation I was having days before. Well i would like to know, what if we need to extract data from 2 cells. Eg, in cell A2, I have 467John & in cell B5, I have just 467.. Is there any way I could extract just 467 in cell C. ?

[@jgbalboa]

Yes, that is simpler. Just place the formula in cell C (for example) =LEFT(A2,SEARCH(" ",A2)+0) I prefer "SEARCH" instead of "FIND" (find has problem with uppler/lower case difference.)

[@ExcelSquad]

Find is a case sensitive. Search is not case sensitive

[@ithaanil2775]

It is very important in excel

[@rameshwarshukla3413]

I need ur help to find Dr only..can u plz help me.

[@tranglieu7993]

This is sooooo awesome!!!! Thank you so much !!!!

[@soonny002]

Is it possible to "find" a particular character on an entire row rather than just a cell? How do you do that? Thanks...

[@prasadphani6823]

did u get how to do it? can u share if you know it?

[@soonny002]

Oh, it's been a long time. I remember doing it but not exactly how. Lol. Just google it.

[@prasadphani6823]

Oh ok anyway thanks for your reply. I am trying with or statement

[@ModiShah]

I want to del first two numbers from a column of phone numbers. How to do it? plz help

[@martinsger]

=left(a1;2) in A1, put to phone number...

[@6SpeedRobbyG]

I have a set of user info that I need to sort alphabetically by last name. The string values imported of 800+ users is generally "Bob Saggot - information technology". So FIRST LAST - DESCRIPTION. How could I separate the first names out to make new column with them and then sort by last name.

[@SIMRANKAUR-eg7nc]

Text to columns. I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.

[@samlee547]

i have a similar problem i have a text string of time that is not well captured (12:29PM or 11:39AM) i want a formula that can separate for me the PM and AM so i can get the time value i need, any Help please??

[@SIMRANKAUR-eg7nc]

Very simple. Use fixed width. I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.

[@saamuttan]

plzzz help sir suppose i hav a date in cell A6. in down there is paragraph so i want that date in the A6 cell in the paragraph

[@TeachExcel]

Upload a sample file and your question in our forum and we can help! www.teachexcel.com/talk/microsoft-office?src=yt

[@saraliseth]

thanks, its really helpful plus i love your voice!!! lol

[@prasadshetty9743]

Thanks

[@ChickenMaster7]

How can I do the same, but I want to extract the last word. I tried with "right" instead of "left" function, but it cut the words in the middle. Can you help me with this please:)

[@SIMRANKAUR-eg7nc]

Tell more specifically. U can however try video on "off to office"

[@lostlonelydude]

Hi I have a question. I don't know if a function like this exists or well I think anything is possible with excel! So I am using the following function for a project: MID($A1, COLUMNS($C1:C1), 1) so the outcome appears on C1. and this is to get a certain set of numbers or words on a certain cell to appear on different cells. I would like to know if there is a formula or a way I can create a macro for this outcome to appear from back to front. Like say there is a range A:1 to A15 I want numbers to appear from A15 to A1 - back wards. Like the last cell on the selected range to the filled first. Can someone help me with this please. Thank you

[@TeachExcel]

Please ask questions like this on our forum, it will be MUCH easier to help you there. www.teachexcel.com/talk/microsoft-office?src=yt

[@SIMRANKAUR-eg7nc]

2 liner micro. Run a loop of 15 and assign the value with range . Value.if u know a jit if macro u fan do it. I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.

[@ShyamSundar-mn4vm]

Is there any formula to extract the text ifA2 cell has a text string i.e. 2shy564am5 and I want the result shyam

Awesome. Totally perfect!

[@ghaziahmed]

similarly, pls display how to do mid function.....

[@robertweekes5783]

Great post, thanks!

[@MrHareth1983]

great. exactly what i need. i however also need to figure out how to only extract characters after the second space but before a third space.

[@SIMRANKAUR-eg7nc]

I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video...

[@aligh76]

Very helpful thanks

[@ProtonCannon]

I am using exactly this formula, I checked several other websites and all show this formula but when I put it into my excel, even if I copy paste it from another website I always and always get an error message. It reports that the error begins straight at the "A2" after the first parenthesis. I could not find any solutions to this problem. Could someone please help?

[@saqibmudabbar]

ProtonCannon Probably your A2 is empty

[@jobenjames8074]

Thanks.

[@TeachExcel]

You are very welcome! I'm glad to help)))

[@nissegurraakter]

Well I cant get the function to work in office 2013. Don't know what has changed...

[@davidcjay]

+nissegurraakter the FIND function is obsolete, and has been replaced by the SEARCH function

[@ioa5s]

PERFECT!

[@TheEfian2004]

what if i just wanted everything to the left if the comma

[@davidcjay]

+TheEfian2004 you would use the SEARCH function. Find is kind of obsolete. It would be =LEFT(A1,SEARCH(",",A1)-1) You can also use the MID function to extract words from the middle of a text string

[@TheEfian2004]

thank you

[@aslivinschi]

Dear all, How could I write a formula to separate all this months in separated cells? many thanks April/Oct Feb/May/Aug/Nov March/Sept Feb/May/Aug

[@SouthAfricanVoice786]

What if the first words are within “ghhhd” quotes

[@nihilsoni4098]

My question is if i have two sentence like ((1)i have 20number of car (2)i dont no this 35number car) if In this 2 sentence I want 20 number and 35 number so which formula i can use

[@raghulkrishna5089]

what we do ., if we dont have a space

[@TeachExcel]

Use another delimiter and or Text-to-Columns, which also works if the text is always the same length but there is no delimiter.

[@caxxadorm]

very helpfull, thanks

[@raihanuddin309]

Thank You

[@saurabhprajapati3753]

why not simply use "flash fill" ??

[@SatishKumar-og1ji]

Hello could [lease help me on this..#Opportunities=234#Defects=56 i'm trying to extract only numbers (234 and 56) How i do this?

[@TeachExcel]

I can yes and I will point you in the right direction first so that you can learn how to do it: combine FIND() and MID() functions for the first number and search on the equals sign and for the second use of FIND() search on the pound sign (#) - for the second number, use RIGHT() and FIND() on the equals sign.

[@LeticiaQuezada-mf8rk]

@@TeachExcel 7:27

[@marceloribeirosimoes8959]

The issue is when the character used to be a parameter is not located in the same position...

[@SIMRANKAUR-eg7nc]

At the end of the day there have to have a patter. Delimiters at most of times helps but if u find some consistently or pattern u can use fixed width and put a break line to fetch the desired result.

[@SIMRANKAUR-eg7nc]

I have really racked my brain a lot going one videos to another. I came across a channel "off to office". Delimiters,fixed width, qualifier, dd.mm.yyyy to dd/mm/yyyy everything have been explained in one single video.

[@abhiseksingh4886]

@@SIMRANKAUR-eg7nc thanks it helped.mam I subscribed.

[@LeticiaQuezada-mf8rk]

6:49

[@sikorloa]

How come this doesn't work in Excel 2013?

[@davidcjay]

+sikorloa use the SEARCH function instead

[@shafeekmc9430]

itz very helpfull us

[@carlosvipe2765]

Microsoft should make it much simpler than that, I presume.

[@ahmadfadhli4386]

09:22

[@ashoktripathi9509]

गुड मॉर्निंग सुप्रभात आपका दिन मंगलमय हो

[@BUTASINGH-qr2sz]

---------------my name and occupation------ how write this line in excel