Excel - COLUMN Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc