Show the first name of text rather than the full name

[KClem]

I have a list of names like James John Jones, in one cell. I need a formula which will just pick up 'James'. I need it to separate up to the first space. However, if there is more than one person with the first name it needs to show the first and second name. So If there were two people called James, it should show as James John.


Thanks

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[shukla.ankur281190]

Assuming A1 cell having the naming
Perhaps

B1=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>=2,TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",50)),100)),LEFT(A1,FIND(" ",A1)-1))

[KClem]

I have tried from my PC and mac to use the attachment button and it doesn't work. There maybe a problem with the website. I will Try again later. In the meantime here is what i put together

Original List
Ayomi Helen Gregson-Lloyd
Sami Grzelecki
Janelle Angela Hamilton
Janelle Susan Hamilton
Shiya-Louise Hassan
Kimberly Hazlewood
Seron Heathcote

Result 1
Ayomi
Sami
Janelle
Janelle
Shiya-Louise
Kimberly
Seron

Result 2
Ayomi
Sami
Janelle Angela
Janelle Susan
Shiya-Louise
Kimberly
Seron


I can work with the result 1 list, but would prefer result 2 if it is possible.

[shukla.ankur281190]

try

Result1 B1=LEFT(A1,FIND(" ",A1)-1)
Result2 C1=IF(SUMPRODUCT(--($E$1:$E$7=E1))>=2,TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",50)),100)),LEFT(A1,FIND(" ",A1)-1))


Drag down.

[KClem]

Thanks Anukur

[shukla.ankur281190]

Glad it helps you