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