****** 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
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.
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.
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 . ua-cam.com/video/FbcXl4BP0AI/v-deo.html
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.
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
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
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 . ua-cam.com/video/FbcXl4BP0AI/v-deo.html
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.
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 . ua-cam.com/video/FbcXl4BP0AI/v-deo.html
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.
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 . ua-cam.com/video/FbcXl4BP0AI/v-deo.html
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.
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.
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")
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.
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.
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 . ua-cam.com/video/FbcXl4BP0AI/v-deo.html
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
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..
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??
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.
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.
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 . ua-cam.com/video/FbcXl4BP0AI/v-deo.html
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?
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.
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.
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!!
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.
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
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.
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?
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.
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...
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
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.
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.
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.
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
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:)
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. ?
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.)
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?
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.
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??
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.
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
+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
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.
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.
SUBIC CLIENTS:DATU MOHAMMAD ALI JAWAWI SEMA SUBIC CLIENTS:DAVE BURGESS SUBIC CLIENTS:DAVID MAC CORMICK SUBIC CLIENTS:DAVID PIO I need to separate Name from the First two words Please help
Pls find the attached file. What im looking to get is province name in coloumn B of "Address" sheet. Province names are listed in the sheet "Province" Any help is greatly appreciated. Thanks for your time. drive.google.com/file/d/1gEUX8QJ8rugKYACuYRISCAI3MmWlOM5i/view?usp=sharing
Text to column. Delimit with comma and concatenate accordingly 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.
Sir, I request you to please make a formula in which I can extract two or three words from the middle of the line of a cell in Excel. I have made different formulas for both, but I am not able to form a formula by mixing them both. I am sharing both those formulas with you. You are requested to mix these two and make a formula so that I can extract two or three or four words from a line in a cell. Please Please (To extract one word from mid of line) =Trim(MID(SUBSTITUTE(B6," ",REPT(" ",LEN(B6))),(C6-1)*LEN(B6)+1,LEN(B6))) (To extract 2 or 3 word from the starting of the line) =TRIM(LEFT(B1,FIND("~",SUBSTITUTE(B1," ","~",A1)&"~")))
****** 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
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.
Give the man a hand, sooo helpful, thank you
Great teaching!!! Thank you.
Q: How can you extract the City (second last to the right) with a right function?
THANKS!
=LEFT(A2,FIND(" ",A2,1+FIND(" ",A2,1+FIND(" ",A2)))-1)
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.
Thanks. This was so helpful for me.
Million thanks .. your sharing is very helpful. You made my day.
Exactly what I needed thanks so much!
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 .
ua-cam.com/video/FbcXl4BP0AI/v-deo.html
amazing, it was very hard but you made it easy.
I want to select only 2 numbers from left , please guide
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.
haha enjoying your video from Spring, TX
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
HI, Could you help me, how to copy particular last cell from the data
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
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.
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 .
ua-cam.com/video/FbcXl4BP0AI/v-deo.html
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.
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 .
ua-cam.com/video/FbcXl4BP0AI/v-deo.html
what about if I only need the text after the second space?
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.
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 .
ua-cam.com/video/FbcXl4BP0AI/v-deo.html
Very helpful and easy to follow. This is EXACTLY what I needed.
Thank you bro, Please may I know other types of data extraction available, can i use regex, python, pandas and VBA?
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.
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.
Absolutely what i was looking for. Thank you.
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")
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.
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.
Thanks a lot. I finally got this!!!
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 .
ua-cam.com/video/FbcXl4BP0AI/v-deo.html
How to extract any value from the cell e.g last value.I tried this formula but it shows the start value.
How to do it other way around, If I want to insert a value taken form another cell into an existing string ?
Concatenate
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 thank you
Thank you so much it worked 🙂
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.
Right and len in combination with substitute
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..
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??
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.
hi, what if i want to use the second word only?
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
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.
Saved my time.
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 .
ua-cam.com/video/FbcXl4BP0AI/v-deo.html
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?
Is there any formula to extract the text ifA2 cell has a text string i.e. 2shy564am5 and I want the result shyam
Hi, how do i extract the last 3-4 words of a cell?
It helped a lot. Thanks
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.
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.
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!!
Select the cell, then go to data, select text to columns and separate by the appropriate symbol
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.
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
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
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.
This was very helpful. Thank you!!!
Fantastic video. Thank you very much!
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?
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.
Excellent sir, thanks- shivsharan
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.
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...
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
I want to del first two numbers from a column of phone numbers. How to do it? plz help
=left(a1;2) in A1, put to phone number...
I just love it.
It reduces my effort
How would i pull out just Bridgegate and Tx? or 6618 and Spring?
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.
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
Upload a sample file and your question in our forum and we can help! www.teachexcel.com/talk/microsoft-office?src=yt
May i use vlookup with this extract???? Please let me know.
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.
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.
Wow!!! You are really smart & a very good teacher!
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.
how would i pull just the bridgegate dr part in this?
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
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:)
Tell more specifically. U can however try video on "off to office"
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. ?
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.)
Find is a case sensitive. Search is not case sensitive
Thank you very much very helpful
Is it possible to "find" a particular character on an entire row rather than just a cell? How do you do that? Thanks...
did u get how to do it? can u share if you know it?
Oh, it's been a long time. I remember doing it but not exactly how. Lol. Just google it.
Oh ok anyway thanks for your reply. I am trying with or statement
helped a lot, Thanks
How can I do that from the RIGHT side?
Thank you so much. ❤️
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?
ProtonCannon Probably your A2 is empty
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?
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.
@@TeachExcel 7:27
This is sooooo awesome!!!! Thank you so much !!!!
Well I cant get the function to work in office 2013. Don't know what has changed...
+nissegurraakter the FIND function is obsolete, and has been replaced by the SEARCH function
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
thanks, its really helpful plus i love your voice!!! lol
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??
Have u got any answer on this
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.
If there is some kind of pattern u can used fixed width
what if you want a string from the right?
I need ur help to find Dr only..can u plz help me.
Awesome. Totally perfect!
Brilliant 👍👍👍
similarly, pls display how to do mid function.....
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
Great post, thanks!
what if i just wanted everything to the left if the comma
+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
thank you
You the real MVP
what we do ., if we dont have a space
Use another delimiter and or Text-to-Columns, which also works if the text is always the same length but there is no delimiter.
The issue is when the character used to be a parameter is not located in the same position...
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.
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 thanks it helped.mam I subscribed.
Thank you🎉🎉🎉🎉🎉🎉
What if the first words are within “ghhhd” quotes
Very helpful thanks
SUBIC CLIENTS:DATU MOHAMMAD ALI JAWAWI SEMA
SUBIC CLIENTS:DAVE BURGESS
SUBIC CLIENTS:DAVID MAC CORMICK
SUBIC CLIENTS:DAVID PIO
I need to separate Name from the First two words
Please help
Regino Mapa Hey ! Use either one formula or the other :
- MID(A1,FIND(":",A1)+1,LEN(A1)-FIND(":",A1))
- RIGHT(A1,LEN(A1-FIND(":",A1))
Thank you!! It worked!!! Woohooo
It is very important in excel
Mind blowing sir
गुड मॉर्निंग सुप्रभात आपका दिन मंगलमय हो
Microsoft should make it much simpler than that, I presume.
why not simply use "flash fill" ??
Pls find the attached file. What im looking to get is province name in coloumn B of "Address" sheet. Province names are listed in the sheet "Province" Any help is greatly appreciated. Thanks for your time. drive.google.com/file/d/1gEUX8QJ8rugKYACuYRISCAI3MmWlOM5i/view?usp=sharing
very helpfull, thanks
How come this doesn't work in Excel 2013?
+sikorloa use the SEARCH function instead
How to convert date "Monday, September 2, 2019"
to "Monday, 2 September, 2019"
Liking ur comment on 2 sep 2020 😅✌️
Text to column.
Delimit with comma and concatenate accordingly
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.
Sir, I request you to please make a formula in which I can extract two or three words from the middle of the line of a cell in Excel. I have made different formulas for both, but I am not able to form a formula by mixing them both. I am sharing both those formulas with you. You are requested to mix these two and make a formula so that I can extract two or three or four words from a line in a cell. Please Please
(To extract one word from mid of line)
=Trim(MID(SUBSTITUTE(B6," ",REPT(" ",LEN(B6))),(C6-1)*LEN(B6)+1,LEN(B6)))
(To extract 2 or 3 word from the starting of the line)
=TRIM(LEFT(B1,FIND("~",SUBSTITUTE(B1," ","~",A1)&"~")))
Thanks
Thanks.
You are very welcome! I'm glad to help)))
PERFECT!
thanks,
Can you show us how it is works full automatic using chounting the number of spacing ( =LEN(A1)- LEN(SUBSTITUTE(A1," ","")) )