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

Подписаться 251 тыс.

Просмотров 896 тыс.

50% 1

VBA/Macros Course (40% Discount): www.teachexcel.com/vba-course...
More help: www.teachexcel.com
Excel Forum: www.teachexcel.com/talk/micro...
This tutorial shows you how to input a formula into a cell in Microsoft Excel which will pull text from another cell in the spreadsheet or workbook. This is not a macro but a formula.
This particular tutorial describes how to pull text which is separated by spaces. This means you can pull any word from a cell so long as there are spaces in front of it or behind it. This works well for large lists which are formatted exactly the same. Additionally, though this shows you how to extract text using spaces, you can use any distinct symbol or character in a cell to extract the text consistently over a large number of cells.
The functions used include LEFT() and FIND().
To get the spreadsheet used here of for more excel video tutorials, macros, tips and tricks, visit the website www.TeachExcel.com You can also visit the forum there if you have any questions.
Have a great day!

Опубликовано:

5 янв 2009

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 189

@TeachExcel 2 года назад

****** 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.

@hojasderuta 5 лет назад

Give the man a hand, sooo helpful, thank you

@Iam1nsane 3 года назад

Absolutely what i was looking for. Thank you.

@JazzieLadyJ 10 лет назад

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

@jay55patel 5 лет назад

This was very helpful. Thank you!!!

@RaviKumar-uw2ih 6 лет назад

I just love it. It reduces my effort

@datjusthappened101 5 лет назад

Fantastic video. Thank you very much!

@HeadNtheClouds 15 лет назад

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

@kaivanshah4304 3 года назад

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

@srvithal38 2 года назад

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

6 лет назад

Awesome. Totally perfect!

@benwonderfulmedia 3 года назад

Exactly what I needed thanks so much!

@abhiseksingh4886 3 года назад

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 . ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-FbcXl4BP0AI.html

@tranglieu7993 7 лет назад

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

@robertweekes5783 10 лет назад

Great post, thanks!

@Tetchyyy 9 лет назад

You the real MVP

@user-di2sh1lf9h 4 месяца назад

Thanks. This was so helpful for me.

@LorisAyoub 8 лет назад

Thanks a lot. I finally got this!!!

@abhiseksingh4886 3 года назад

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 . ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-FbcXl4BP0AI.html

@victorialee5637 7 лет назад

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

@cibulis5347 2 года назад

It helped a lot. Thanks

@AwaisKhan-tk5xr 11 месяцев назад

helped a lot, Thanks

@payroll6408 4 года назад

Thank you very much very helpful

@khailadelossantos69 3 года назад

Thank you so much. ❤️

@Kinleygyem55 2 года назад

Thank you so much it worked 🙂

@aligh76 10 лет назад

Very helpful thanks

@myleonhomes 2 года назад

haha enjoying your video from Spring, TX

@caxxadorm 12 лет назад

very helpfull, thanks

@mohanpal2737 4 года назад

Mind blowing sir

@ami6737 2 года назад

Thank you!! It worked!!! Woohooo

@ioa5s 5 лет назад

PERFECT!

@saraliseth 13 лет назад

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

@vedantsartcraftanddrawinga4804 3 года назад

Excellent sir, thanks- shivsharan

@Gaurav.YourDedicatedAssistant 2 года назад

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

@raihanuddin309 8 лет назад

Thank You

@kirandas1287 3 года назад

Brilliant 👍👍👍

@subshomegreenhiskills 4 года назад

I need to go through this a few times. My head is spinning with so many find functions and +1 and -1

@wingsofwords6451 3 года назад

Same here

@abhiseksingh4886 3 года назад

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 . ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-FbcXl4BP0AI.html

@prasadshetty9743 2 года назад

Thanks

@Sha306Sha Год назад

Thank you🎉🎉🎉🎉🎉🎉

@ithaanil2775 3 года назад

It is very important in excel

@shafeekmc9430 11 лет назад

itz very helpfull us

@MrHareth1983 5 лет назад

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 3 года назад

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

@psybera1987 8 лет назад

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.

@jobenjames8074 4 года назад

Thanks.

@TeachExcel 4 года назад

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

@irishmumof1 6 лет назад

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 3 года назад

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.

@prakash-uv1ct 9 лет назад

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

@NA-gn2qg 5 лет назад

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.

@jgbalboa 10 лет назад

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 3 года назад

@prakash-uv1ct 9 лет назад

tnx

@gangu1093 4 года назад

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

@ChrisKayMan 8 лет назад

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?

@AmitChauhan004 5 лет назад

Saved my time.

@abhiseksingh4886 3 года назад

@stepfanj3199 6 лет назад

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.

@ghaziahmed 4 года назад

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

@mattshep6610 6 лет назад

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

@TeachExcel 6 лет назад

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

@joarthcarbon9810 3 года назад

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

@ericsontorralba 3 года назад

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

@gvantsagiorgadze9352 2 года назад

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

@Balsero-qb9gv 2 года назад

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 2 года назад

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

@sumitduttagupta2698 4 года назад

Hi how to extract data if entry is same same??

@ranklerave3564 8 лет назад

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.

@granand 3 года назад

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

@TeachExcel 3 года назад

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.

@harshanathawaruna8431 3 года назад

How can I do that from the RIGHT side?

@papas7654 11 месяцев назад

what if you want a string from the right?

@debid44 5 лет назад

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 3 года назад

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.

@irfananwar3447 7 лет назад

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 3 года назад

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.

@rameshwarshukla3413 5 лет назад

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

@carlosrestrepo2041 3 года назад

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

@abhiseksingh4886 3 года назад

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 3 года назад

@Akenazia 6 лет назад

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

@SIMRANKAUR-eg7nc 3 года назад

@mayurkhedekar4441 Год назад

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

@Matilda1804 5 лет назад

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 3 года назад

Right and len in combination with substitute

@SIMRANKAUR-eg7nc 3 года назад

@carlosvipe2765 5 лет назад

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

@mohammadghufrankhan6579 4 года назад

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

@samlee547 5 лет назад

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 3 года назад

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.

@lostlonelydude 5 лет назад

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 5 лет назад

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 3 года назад

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.

@dimitarivanov7249 6 лет назад

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 3 года назад

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

@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

@ModiShah 4 года назад

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

@martinsger 3 года назад

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

@rwalis 4 года назад

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 3 года назад

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 . ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-FbcXl4BP0AI.html

@ShyamSundar-mn4vm 10 месяцев назад

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

@saamuttan 5 лет назад

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 5 лет назад

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

@piplip69 2 года назад

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.

@6SpeedRobbyG 9 лет назад

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 3 года назад

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.

@BUTASINGH-qr2sz 6 лет назад

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

@nissegurraakter 9 лет назад

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

@davidcjay 8 лет назад

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

@raghulkrishna5089 3 года назад

what we do ., if we dont have a space

@TeachExcel 3 года назад

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

@sachina4576 3 года назад

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

@SIMRANKAUR-eg7nc 3 года назад

Concatenate

@SIMRANKAUR-eg7nc 3 года назад

@sachina4576 3 года назад

@@SIMRANKAUR-eg7nc thank you

@furqanessaniACCA 10 лет назад

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 10 лет назад

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.)

@rajuar1955 5 лет назад

Find is a case sensitive. Search is not case sensitive

@ronyshafiq 4 года назад

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

@SIMRANKAUR-eg7nc 3 года назад

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 3 года назад

@soonny002 7 лет назад

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

@prasadphani6823 7 лет назад

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

@soonny002 7 лет назад

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

@prasadphani6823 7 лет назад

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

@ProtonCannon 7 лет назад

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 7 лет назад

ProtonCannon Probably your A2 is empty

@nextdoortechieaminfirnash6002 5 лет назад

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.

@SouthAfricanVoice786 2 года назад

What if the first words are within “ghhhd” quotes

@ashoktripathi9509 3 года назад

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

@aslivinschi 2 года назад

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

@saurabhprajapati3753 4 года назад

why not simply use "flash fill" ??

@DesignCenterPEC 11 лет назад

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 4 года назад

Have u got any answer on this

@SIMRANKAUR-eg7nc 3 года назад

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

@TheEfian2004 8 лет назад

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

@davidcjay 8 лет назад

+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 8 лет назад

thank you