Question description:
This user has given permission to use the problem statement for this
blog.
Hi, I am new here ! Bit stumped on an excel problem and i am sure the solution is simple. Normally i would use a left, mid or right function to extract data from cells. However, my problem is that i have been provided a list of 100's of names all in column A in the format MR/MRS/MISS etc followed by first name followed by surname all in one cell - all of which are different lengths. There is a space between the title and the first name and again for the surname. Is there a function that will look at the second block of text within a cell and pull that out? so i could set up a formula to pull the first name out then again for the surname ?
Solved by O. J. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/06/2018 - 04:51
Hi…Welcome to Got It Pro
User
08/06/2018 - 04:51
Hi
User
08/06/2018 - 04:51
shall i fill in a cell so you can see what i am talking about?
Excelchat Expert
08/06/2018 - 04:52
sure...that will be of great help.
Excelchat Expert
08/06/2018 - 04:53
As per your problem statement, I understand that you are trying to pull out the first and last name from the cell, is that right?
User
08/06/2018 - 04:53
so instead of manually doing mids and rights for each cell i just want a formula that will pull the first name out into one column then the surname into another column
User
08/06/2018 - 04:53
yeah
User
08/06/2018 - 04:54
the list has about 1500 names - too long to do it manually
Excelchat Expert
08/06/2018 - 04:54
Does the current name look something like what I have placed in cell A1?
User
08/06/2018 - 04:55
yeah
User
08/06/2018 - 04:55
great !
Excelchat Expert
08/06/2018 - 04:55
I am yet to do the formula :)
User
08/06/2018 - 04:55
ah :-(
Excelchat Expert
08/06/2018 - 04:56
Let me build the formula which you can just drag down.
User
08/06/2018 - 04:56
ok thanks ! :-)
Excelchat Expert
08/06/2018 - 05:02
Last name is extracted
Excelchat Expert
08/06/2018 - 05:02
I am working on the first name
User
08/06/2018 - 05:02
Thanks !
User
08/06/2018 - 05:07
what happens when the timer runs out ?
User
08/06/2018 - 05:07
we're at 4 minutes
Excelchat Expert
08/06/2018 - 05:07
it will get extended
User
08/06/2018 - 05:07
ah right !it's a tricky one this !
Excelchat Expert
08/06/2018 - 05:10
ok..you have the first name as well
User
08/06/2018 - 05:11
great !
Excelchat Expert
08/06/2018 - 05:11
Yeah..the formula is now dynamic and it will work with any length of first and last name
Excelchat Expert
08/06/2018 - 05:11
You can try any name
User
08/06/2018 - 05:11
can it be amended if theres a middle name ?
Excelchat Expert
08/06/2018 - 05:12
The formula can be amended
User
08/06/2018 - 05:12
NICE !
User
08/06/2018 - 05:12
how do i get the formula ?
Excelchat Expert
08/06/2018 - 05:12
You can download this file.
User
08/06/2018 - 05:13
how do i do that ?
Excelchat Expert
08/06/2018 - 05:14
Please click on File > Download As > MS Excel
User
08/06/2018 - 05:15
nothing happens when i do that
Excelchat Expert
08/06/2018 - 05:15
Ok...let me upload this same file as excel here so that you can easily download.
User
08/06/2018 - 05:16
ok
Excelchat Expert
08/06/2018 - 05:16
Here you go
[Uploaded an Excel file]
Excelchat Expert
08/06/2018 - 05:17
Are you able to download it?
User
08/06/2018 - 05:19
yeah, i have managed it - thanks so much ! Whats your name ?
Excelchat Expert
08/06/2018 - 05:19
I am Dan
Excelchat Expert
08/06/2018 - 05:20
Is there anything else I can help you with this query?
User
08/06/2018 - 05:21
Cheers Dan, no that's it thanks ! So, out of interest, as i am new here - is this a british website or are you based somewhere else?
Excelchat Expert
08/06/2018 - 05:21
We are based out of US
Excelchat Expert
08/06/2018 - 05:21
But our experts are from all over the globe :)
User
08/06/2018 - 05:22
Wow ! I am in UK, love going over to USA, I have been Alaska, Hawaii, Florida, Boston , New York, Seattle , Vegas, LA and San Dieo
Excelchat Expert
08/06/2018 - 05:22
And we cater to Users/Students from around the world.
User
08/06/2018 - 05:23
by the way sorry, how do i amend the formula to extract a middle name ?
Excelchat Expert
08/06/2018 - 05:23
That's really nice...you have travelled quite a lot
Excelchat Expert
08/06/2018 - 05:23
ok...let me do that for you.
User
08/06/2018 - 05:25
thanks - i just need to leave my computer for a while, can you email me it? mr.c.jones@icloud.com thanks for everything ! All the best !
Excelchat Expert
08/06/2018 - 05:34
In the excel file that I sent you, please place this formula in cell D1
Excelchat Expert
08/06/2018 - 05:34
=Iferror(MID(TRIM(SUBSTITUTE(A1,LEFT(A1,FIND(" ",A1)-1),"")),SEARCH(" ",TRIM(SUBSTITUTE(A1,LEFT(A1,FIND(" ",A1)-1),"")),1)+1,SEARCH(" ",TRIM(SUBSTITUTE(A1,LEFT(A1,FIND(" ",A1)-1),"")),SEARCH(" ",TRIM(SUBSTITUTE(A1,LEFT(A1,FIND(" ",A1)-1),"")),1)+1)-SEARCH(" ",TRIM(SUBSTITUTE(A1,LEFT(A1,FIND(" ",A1)-1),"")),1)),"")
Excelchat Expert
08/06/2018 - 05:34
This will extract the middle name if any
Excelchat Expert
08/06/2018 - 05:35
Unfortunately, I could not view your last comment as I believe it content some sensitive information. Could you please restate your last comment?
Excelchat Expert
08/06/2018 - 05:36
On this sheet, you will see the Middle name '"D" is extracted.
Excelchat Expert
08/06/2018 - 05:37
You can drag down the formula.
User
08/06/2018 - 05:37
Got it ! Thats great thanks so much - sorry my wife keeps dragging off for dinner ! Cheers and all the best !
Excelchat Expert
08/06/2018 - 05:37
:)
Excelchat Expert
08/06/2018 - 05:37
Please do give me a good feedback for my service.
Excelchat Expert
08/06/2018 - 05:37
Thanks for your time. Please do come back for any new question.
Excelchat Expert
08/06/2018 - 05:37
Have a happy Dinner and a great weekend!!!
Excelchat Expert
08/06/2018 - 05:38
You can now end this session. Thanks!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
