I had to watch 12 different videos before I found one to help me out. I was looking to save time not use up more time than deleting the data 1 by 1 in each cell. You are the only one who provided a simple formula for this process. You are a life saver. THANK YOU!!!!!!!!
The formula is good for disparate fields (i.e. one field has first name, middle initial and last name) and another data field same column only has first and last name or the spacing in the field is different. Just depends how the data was initially sent. So thanks for the formula!
Okay, I figured out the right way to do this. First, use Data > Text to Columns to separate the source by spaces, then add a blank column B. You'll have first names in column A, nothing in column B, a mix of middle and last names in column C, and a column D that has last names only if there was a middle name. Names are messy things, so you need to check if there is anything in columns E and F in case a subject has two middle names and/or a first or last name with a space in it (Jo Anne P De Tomaso will become 5 columns). Fix these individually. (In this video's example, you'd also have a column of the dates of office.) Now use Data > Filter and filter column D for non-blanks. Now you've isolated the rows where the source had a middle name, all you have to do is move C & D one column to the left. Unfortunately, you can't simply cut and paste because the filtered cells confuse Excel. Instead, fill column B with a formula that copies column C (=C1, =C2, etc.); since it's filtered, only the cells that are showing will get the formula. Then you'll have to clear the filter. Column B will now be a mix of middle names and, where the source had no middle name, blanks. Copy column B and paste it on itself with the Paste With Values option. Now you've got a real middle name column. Next, do the same for column C. That is, filter D for non-blanks and fill C with the formula =D1, =D2, etc. And then clear the filter and copy and paste C on itself as values. Now you've got a real last name column. Finally, delete column D.
This is terrific and very well explained, but I notice that when you finished and you copied your formula down to the rows below, you always stopped at the ones that have a middle name or middle initial (like John Quincy Adams and James K. Polk. Will your formulas know how to ignore the middle name or middle initial? Do you have a video that shows formulas to accommodate the variance in the souce column's data?
As pointed out by others, this method fails when the string in column A contains more than three elements. For example: First, Middle, Last, and Dates will not be parsed correctly. I would approach this from a different direction. First I would separate the dates into a target column. The following formula will accomplish that: =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) The following formula will pull everything EXCEPT the dates (or last word) from the source cell. Using this approach isolates the variable length text from the 11-character dates being shown in this video. =LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1) The above formula also helps deal with multiple spaces sometimes found in text strings. However, it does not deal with non-printing characters, which would require using the CLEAN function in addition to TRIM. Non-printing characters can be found in text strings that are imported from other systems but are not typically found in cells that have been entered manually. The second formula above leaves you with the need to deal with the variable number of elements in the name. There are other videos which explain the LEFT, MID, and RIGHT functions to accomplish that. The following video provides one method to extract the middle name from the source cell: v=9V7F3wi3Sp8 As you can see, this video did not go far enough in explaining how to manipulate a "real world" problem you might encounter. On the other hand, if your source text is very structured and consistent, you can 'get by' with the example formulas shown in this video.
What about the cell with the 3 spaces in it? John Quincy Adams for example. If you pulled your formula down, it would not work for that cell. Any way to fix this? so that it doesn't matter how many spaces or different amount of spaces in a cell - and use the same formula?
Hey John , I am from India . . . . If any name which have 3 spaces So you can use my this formula to get the last name from it. "=RIGHT(D5,LEN(D5)-FIND(" ",D5,FIND(" ",D5,FIND(" ",D5)+1)+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 . ruclips.net/video/FbcXl4BP0AI/видео.html
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!!
Hello. Just substitute the “-“ for the space “ “. So instead of finding the space ” “ in A4, you type “-“ and follow the rest of the formula as stated. This also applies to ANY separator like a period, comma or colon or whatever is separating the words.
Really nice but if you could elaborate on the formulas it would have been better to understand on the formulae however thanks for this video I trust it will help everyone keep going
Do chek this 1. While I appreciate the logic for extradition of last word, I found far easier and less time taking method to achieve the same without len and substitute .Totally a must watch . ruclips.net/video/FbcXl4BP0AI/видео.html
thanks it's was great explanation. I tried but when I wanted to organize my table I deleted the left part I mean the all together part. it all disappeared. how I do I prevent this? I also copy the separated part on the next sheet it didn't work. help please thanks :)
..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 . ruclips.net/video/FbcXl4BP0AI/видео.html
Or insert a cell to the right of the one you want to extract the names and dates from, Type the first name, hit enter, press control E. Repeat in additional columns as needed
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.
Use text to column instead. If required use fixed width in n lieu of delimiters .A must watch video for u form my side ruclips.net/video/RhamuSEieQ0/видео.html
Good grief. This doesn't work even for the example he's chosen. Notice that he stops at John Quincy Adams because the middle name messes up his method. Excel can split cells very easily with Data > Text to Columns, but it has the same problem of being unable to deal with middle names and with last names with a space in them. It just spreads them out in separate cells, creating columns that mix middle and last names. This is even worse.
Hey John , I am from India . . . . If any name which have 3 spaces So you can use my this formula to get the last name from it. "=RIGHT(D5,LEN(D5)-FIND(" ",D5,FIND(" ",D5,FIND(" ",D5)+1)+1))
Please Help. how to extract the: address--city--state--zip code to get each in it's own cell; AND HAS NO COMMA after the street address? EX 12345 NE 6 AVE North Miami Beach, FL 33339
To extracting middle portion of string with MID function "=MID(A1,FIND(" ",A1)+1,FIND(" ",A1)-1)" this formula is ok, can you explain, why need to add more find function with it.
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 . ruclips.net/video/FbcXl4BP0AI/видео.html
Hey that was very useful. but i have a doubt could you please clarify it please. what if the primary cell has different set of words (Ex: The first cell has 4 words and the second cells has 6 words in it how can it be used? Will this formula be in use in this case if another please suggest?)
Hey John , I am from India . . . . If any name which have 3 spaces ( means 4 ) words So you can use my this formula to get the last name from it. "=RIGHT(D5,LEN(D5)-FIND(" ",D5,FIND(" ",D5,FIND(" ",D5)+1)+1))
@@abinashpraharaj3614 either use text to columns. For splitting all three and then removed the 1st and 3rd column to get only the second. ruclips.net/video/RhamuSEieQ0/видео.html
Hi,If i have a name like this James Monroe Andrew, how can i get first character of the first name and first character of the second name and last name together like this J.M.Andrew ? can some one help me with this
Find 1st space with search "space" in text starting ur search from 1st position result: say 5 Find second space Search "space" starting potison this time set to 5+1 =6 result say 14 This way will succeed to find position of 1st and 2nd spaces. U can extra the first charche gee form there using left or mid respectively. For last space : 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 . ruclips.net/video/FbcXl4BP0AI/видео.html
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 . ruclips.net/video/FbcXl4BP0AI/видео.html
Use vba do while loop Extract original number n say 456 I= Int n/10 gives u 45 Now r=n-i*10 456-450=6 One extracted Now loop appropriately with do while This is the logic. Programme it in any computer language. VBA or anything
If u have to pull every name it will be then be required to be subjected with text to columns feature of excel. 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 . ruclips.net/video/FbcXl4BP0AI/видео.html
सर‚ यदि मान लीजिए एक्सेल में कोई कॉलम हो और उसमें दो डेटा एक ही कॉलम में प्रदर्शित हो रहा है। ऐसी स्थिति मे हम उस एक ही कॉलम के डेटा को अलग-अलग काॅलम में कैसे व्यवस्थित कर सकते है। for Example - B290000185076,11389
I can use fixed width feature of text to columns. C it. Must watch from my side as I find all comments here with no actual problem. ruclips.net/video/RhamuSEieQ0/видео.html
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 . ruclips.net/video/FbcXl4BP0AI/видео.html
Suggestion: Next time you make a video move your mouse out of the cell, kinda tough to see all of the characters. ALSO, your narration isnt accurate with your entries.
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 . ruclips.net/video/FbcXl4BP0AI/видео.html
Do chek this 1. While I appreciate the logic for extradition of last word, I found far easier and less time taking method to achieve the same without len and substitute .Totally a must watch . ruclips.net/video/FbcXl4BP0AI/видео.html
Dude....This is the worst way to do it. Just opendata, text to columns, select space as the delineation and go.....does the whole thing in less than a minute. Nobody uses this formula.
hello! So I have this string: TP 80-110/4-A-F-A-BAQE 400D 50HZ Cod produs: 96108837 I want to extract from this string only the number after "Cod produs", meaning 96108837. How can I do that? Thank you!
I had to watch 12 different videos before I found one to help me out. I was looking to save time not use up more time than deleting the data 1 by 1 in each cell. You are the only one who provided a simple formula for this process. You are a life saver. THANK YOU!!!!!!!!
Thank you very much for the video:) Great explanation. Much appreciated it!!!
Great formula and good explanation. This is one of the finest video on excel. Every body should watch.
Best Regards
This was greatly helpful. Appreciate man.
This was very helpful. Thank you!!!
The formula is good for disparate fields (i.e. one field has first name, middle initial and last name) and another data field same column only has first and last name or the spacing in the field is different. Just depends how the data was initially sent. So thanks for the formula!
REALLY EXCELLENT STUFF... HAD BEEN SEARCHING FOR THIS FOR VERY LONG TIME !!
great video mate!
Great!!! Million Thanks
Okay, I figured out the right way to do this. First, use Data > Text to Columns to separate the source by spaces, then add a blank column B. You'll have first names in column A, nothing in column B, a mix of middle and last names in column C, and a column D that has last names only if there was a middle name.
Names are messy things, so you need to check if there is anything in columns E and F in case a subject has two middle names and/or a first or last name with a space in it (Jo Anne P De Tomaso will become 5 columns). Fix these individually. (In this video's example, you'd also have a column of the dates of office.)
Now use Data > Filter and filter column D for non-blanks. Now you've isolated the rows where the source had a middle name, all you have to do is move C & D one column to the left. Unfortunately, you can't simply cut and paste because the filtered cells confuse Excel. Instead, fill column B with a formula that copies column C (=C1, =C2, etc.); since it's filtered, only the cells that are showing will get the formula. Then you'll have to clear the filter. Column B will now be a mix of middle names and, where the source had no middle name, blanks. Copy column B and paste it on itself with the Paste With Values option. Now you've got a real middle name column.
Next, do the same for column C. That is, filter D for non-blanks and fill C with the formula =D1, =D2, etc. And then clear the filter and copy and paste C on itself as values. Now you've got a real last name column. Finally, delete column D.
Thank you so much for this video, It was really helpful.
Thank you HERO!
Awesome!
Helped a lot, thanks!
This is terrific and very well explained, but I notice that when you finished and you copied your formula down to the rows below, you always stopped at the ones that have a middle name or middle initial (like John Quincy Adams and James K. Polk. Will your formulas know how to ignore the middle name or middle initial? Do you have a video that shows formulas to accommodate the variance in the souce column's data?
hi, hope this video URL would help your above query ruclips.net/video/zghj4leb2dg/видео.html
As pointed out by others, this method fails when the string in column A contains more than three elements. For example: First, Middle, Last, and Dates will not be parsed correctly.
I would approach this from a different direction. First I would separate the dates into a target column. The following formula will accomplish that:
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
The following formula will pull everything EXCEPT the dates (or last word) from the source cell. Using this approach isolates the variable length text from the 11-character dates being shown in this video.
=LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)
The above formula also helps deal with multiple spaces sometimes found in text strings. However, it does not deal with non-printing characters, which would require using the CLEAN function in addition to TRIM. Non-printing characters can be found in text strings that are imported from other systems but are not typically found in cells that have been entered manually.
The second formula above leaves you with the need to deal with the variable number of elements in the name. There are other videos which explain the LEFT, MID, and RIGHT functions to accomplish that.
The following video provides one method to extract the middle name from the source cell:
v=9V7F3wi3Sp8
As you can see, this video did not go far enough in explaining how to manipulate a "real world" problem you might encounter. On the other hand, if your source text is very structured and consistent, you can 'get by' with the example formulas shown in this video.
also you can use data >> text to column easily instead of the complicated formula
thank you very much sir, i was looking for this formula... finally i got it.(2021)
Thank You!
no no....bundle of thanks! :)
Very helpful.. thank you
Thank you a bunch!
Awesome !!! Thx
Who would select 'thumbs down' on this video? Geeze. Great job, sir!
What about the cell with the 3 spaces in it? John Quincy Adams for example. If you pulled your formula down, it would not work for that cell. Any way to fix this? so that it doesn't matter how many spaces or different amount of spaces in a cell - and use the same formula?
Hey John , I am from India . . . . If any name which have 3 spaces So you can use my this formula to get the last name from it. "=RIGHT(D5,LEN(D5)-FIND(" ",D5,FIND(" ",D5,FIND(" ",D5)+1)+1))
This formula does not work, just like India's top order batting.
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 .
ruclips.net/video/FbcXl4BP0AI/видео.html
Sooo, helpful!
Thank you !!!
Thanks sir💐
thank you
I had an issue with the (fx) doesn't except (Find) I had a message say that it's incorrect show me that left(text; [num_chars])
it's great thanks a bunch. What about the one that have more than one middle name?
besides spaces, what else can you find using this formula?
Super sir...
Greate video thanks
hey i want to copy text from an entire row and use it in word. how do i do that pls help
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!!
Hello. Just substitute the “-“ for the space “ “. So instead of finding the space ” “ in A4, you type “-“ and follow the rest of the formula as stated.
This also applies to ANY separator like a period, comma or colon or whatever is separating the words.
Really nice but if you could elaborate on the formulas it would have been better to understand on the formulae however thanks for this video I trust it will help everyone keep going
Do chek this 1. While I appreciate the logic for extradition of last word, I found far easier and less time taking method to achieve the same without len and substitute .Totally a must watch .
ruclips.net/video/FbcXl4BP0AI/видео.html
thanks it's was great explanation. I tried but when I wanted to organize my table I deleted the left part I mean the all together part. it all disappeared. how I do I prevent this? I also copy the separated part on the next sheet it didn't work. help please
thanks :)
What about the ligne n8
John Quincy Adams (1825-1829)
thanks loads
thank you very mach dear, this is Great formula and good explanation thank you again
Best Regards
..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 .
ruclips.net/video/FbcXl4BP0AI/видео.html
@@abhiseksingh4886 thank u so much for sharing this link. It saved my life.
How do remove the parentheses in the year column?
please attached the download with this video! many thanks
Or insert a cell to the right of the one you want to extract the names and dates from, Type the first name, hit enter, press control E. Repeat in additional columns as needed
Wtf!? Thank you, Sir!!! I was right to look at the comments to find some shortcuts and wasn't disappointed. I found this gem! Thank you so much!
I have an excel list 900 numbers in one single cell. I want to put each number in its individual cell. How can I do that? Please advise.
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.
Use text to column instead. If required use fixed width in n lieu of delimiters .A must watch video for u form my side
ruclips.net/video/RhamuSEieQ0/видео.html
Good grief. This doesn't work even for the example he's chosen. Notice that he stops at John Quincy Adams because the middle name messes up his method. Excel can split cells very easily with Data > Text to Columns, but it has the same problem of being unable to deal with middle names and with last names with a space in them. It just spreads them out in separate cells, creating columns that mix middle and last names. This is even worse.
Hey John , I am from India . . . . If any name which have 3 spaces So you can use my this formula to get the last name from it. "=RIGHT(D5,LEN(D5)-FIND(" ",D5,FIND(" ",D5,FIND(" ",D5)+1)+1))
Great
Please Help. how to extract the: address--city--state--zip code to get each in it's own cell; AND HAS NO COMMA after the street address? EX 12345 NE 6 AVE North Miami Beach, FL 33339
To extracting middle portion of string with MID function "=MID(A1,FIND(" ",A1)+1,FIND(" ",A1)-1)" this formula is ok, can you explain, why need to add more find function with it.
What if the string has 3 or 4 spaces. No of search function will increase. Also if there are 100 rows with different no of spaces thus may not work.
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 .
ruclips.net/video/FbcXl4BP0AI/видео.html
Hey that was very useful. but i have a doubt could you please clarify it please. what if the primary cell has different set of words (Ex: The first cell has 4 words and the second cells has 6 words in it how can it be used? Will this formula be in use in this case if another please suggest?)
Hey John , I am from India . . . . If any name which have 3 spaces ( means 4 ) words So you can use my this formula to get the last name from it. "=RIGHT(D5,LEN(D5)-FIND(" ",D5,FIND(" ",D5,FIND(" ",D5)+1)+1))
@@TeachToEach hi bro. What if I want to split 3 words and I want to keep 2words in middle name. Is it possible? If possible please help me.
@@abinashpraharaj3614 either use text to columns. For splitting all three and then removed the 1st and 3rd column to get only the second.
ruclips.net/video/RhamuSEieQ0/видео.html
Or use left right twice. But after finding spaces. Can refer to it.
ruclips.net/video/FbcXl4BP0AI/видео.html
Hi,If i have a name like this James Monroe Andrew, how can i get first character of the first name and first character of the second name and last name together like this J.M.Andrew ? can some one help me with this
Find 1st space with search "space" in text starting ur search from 1st position result: say 5
Find second space
Search "space" starting potison this time set to 5+1 =6 result say 14
This way will succeed to find position of 1st and 2nd spaces.
U can extra the first charche gee form there using left or mid respectively.
For last space :
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 .
ruclips.net/video/FbcXl4BP0AI/видео.html
what is the formula to extract double first name?
Example name
MARK JAMES S. YAP
I wanto to extract MARK JAMES.
What formula will I use?
Thank you.
amizing
how about if we want the middel 2 digit
Helped me a lot thanks v.much. (@$#)
What will do, If only two names and pull up lat name and second last name?
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 .
ruclips.net/video/FbcXl4BP0AI/видео.html
How do you split names with two first names? Ex. John MIchael P. Jones
How about with multiple first names and two last names example: Thorton Alton, John Levy Ion(first names) Mendes (Middle). Please help.
If Multipal Number in One Row So What can i Do
What about that 8th row
How to sum 123456789 value if this value in one cell.
This is pure programming logic man.
Use vba do while loop
Extract original number n say 456
I= Int n/10 gives u 45
Now r=n-i*10
456-450=6
One extracted
Now loop appropriately with do while
This is the logic. Programme it in any computer language. VBA or anything
To find John Quincy Jones just add +1
..for excel 2007 try use =LEFT(A1;FIND(" ";A1)-1) with semicolons because with comas it doesn't work for me..
You didnt pull the middle name. Only the first and the last name
If u have to pull every name it will be then be required to be subjected with text to columns feature of excel.
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 .
ruclips.net/video/FbcXl4BP0AI/видео.html
What if there are more than 3 columns
I don't understand how u get middle name
For what u use 3-4 times find formula 🤔🤔
You don't explain the what MID, LEFT, and FIND does. Not helpful
True I had to see couple of other videos to understand the concept of starting number
How to separate this " angle boy d. cruz" angel boy is place in one cell and the d. and cruz are separate
KUMAR, VIJAY (BCA) (DU) TO CONVER VIJAY KUMAR . PLS SOLVE IT
सर‚ यदि मान लीजिए एक्सेल में कोई कॉलम हो और उसमें दो डेटा एक ही कॉलम में प्रदर्शित हो रहा है। ऐसी स्थिति मे हम उस एक ही कॉलम के डेटा को अलग-अलग काॅलम में कैसे व्यवस्थित कर सकते है।
for Example - B290000185076,11389
I can use fixed width feature of text to columns. C it. Must watch from my side as I find all comments here with no actual problem.
ruclips.net/video/RhamuSEieQ0/видео.html
your not actually dealing with Middle Name and the initials??
Misleading because everyone thinks you’re going to show even the columns with a middle initial 😒
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 .
ruclips.net/video/FbcXl4BP0AI/видео.html
Tvggwwfrcessecfgdqfffrvdhgdd11xg
Suggestion: Next time you make a video move your mouse out of the cell, kinda tough to see all of the characters. ALSO, your narration isnt accurate with your entries.
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 .
ruclips.net/video/FbcXl4BP0AI/видео.html
You don't even consider it necessary to share the formula in the description.
Do chek this 1. While I appreciate the logic for extradition of last word, I found far easier and less time taking method to achieve the same without len and substitute .Totally a must watch .
ruclips.net/video/FbcXl4BP0AI/видео.html
37wu
Hey guys, how to make this six characters' string into 6 different columns ? 3082B2-313 204489 Automated Y ONGOING N
Zoom karo sir
this method is no good for names with 2 or more in the midddle
bhai itni mara maari ki kya jarurat hai, seedha text to column mein jao, space filter lagao aur teeno naam alag
Not visibility
Dude....This is the worst way to do it. Just opendata, text to columns, select space as the delineation and go.....does the whole thing in less than a minute. Nobody uses this formula.
hello! So I have this string: TP 80-110/4-A-F-A-BAQE 400D 50HZ Cod produs: 96108837
I want to extract from this string only the number after "Cod produs", meaning 96108837. How can I do that? Thank you!