Way to extract the leftmost as well as rightmost characters until first encountered space

Hey there everyone!

What i am trying to do is to separate some names, but this needs to work with a bunch of names, which are very different, some are only first names and last names, and some have up to three middle names. I will give some examples:

Aksel Bertelsen
Anders Bo Friesgaard Christensen
Anita Lisa Georgia James Linde

What i need to do is get the very first name and the very last name in two different cells, so the names would look like this

A1 A2
Aksel Bertelsen
Anders Christensen
Anita Linde

Hope this is something you can help with!

where do the names start? I will assume col a, with col b and col c for the spilt (I know that's not what you said.

for r = 1 to activesheet.usedrange.rows.count
stemp = cells(r,1)
cells(R,2) = left(stemp ,instr(stemp ," ")-1)
cels(R,3 = mid(cells(R,instrrev(stemp ," ")+1)
next

First name:

=LEFT(A1,FIND(" ",A1)-1)

Last name:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

Thank you for the answers

Unfortunately the firstname function does not work, when i enter it, it just returns a blank cell.

The last name function works most of the times, but it also returns a blank at some names, i will post some examples where it returns a blank cell:
Annamaria G. Elena Giraldi
Ann-Charlotte Jensen
Anne A. Elgaard Thorup
Anne Grethe Birch Pedersen

All of the formulas proposed above work fine if your original name/string is in cell A1. Only way you would get blanks is if your original data is in another cell.

- Moo

My original data is in cell A1, then continuing to cell A2, A3 and so on.
But there is an occasional blank cell along the rows

try it
=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",100)),100)&" "&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100)))

Thank you Ghozi! That formula worked perfectly!

Although i still get blanks from the formula for extracting the first name

Hi

How come Ghozi Alkatiri formula works! When in your original post:

What i need to do is get the very first name and the very last name in two different cells, so the names would look like this

A1 A2
Aksel Bertelsen
Anders Christensen
Anita Linde

Kevin: It works to get the last name, which the other formulas didnt accomplish for some reason, i still havent got a formula which can isolate the first name

Exasis,

Perhaps it would help if you uploaded a copy of your file for people to assist with, instead of having us guess as to why it's not working - possibly due to formatting, irregular conditions, etc.

- Moo

Hi exasis

Try assuming you data is in column A
First name:

Formula:

Please Login or Register  to view this content.

Last Name:

Formula:

Please Login or Register  to view this content.

Or are the last names double barrel!

Moo: I would love to if i was allowed, but i had trouble enough being allowed to even show some names, as it is connected to some very sensitive data

See attached:

Thank you Kevin! It worked after i changed the "-1" to "4" for some reason