Forum Discussion

mesmrc's avatar
mesmrc
Copper Contributor
Dec 08, 2020
Solved

Conditional Name-Copying Formula

I have a column of names that contains some cells that are just first name, and others that are first, last, and middle, all in one cell.

 

I'd like to have an adjacent column copy just the first name from the source column while leaving the source column unchanged, as well as capitalize (if not already capitalized) the first character of the copied first name.

 

Can someone help me compose a formula that can do this? The ones I've tried either require the source to have spaces (not applicable to the cells that are only first name currently), or if I do Text-to-Columns it moves the source data rather than copying it, and I need the source column to remain unchanged. Really appreciate the help!

  • mesmrc Hi

     

    Try below formula adjecant to main column:

     

    =PROPER(IFERROR(IF(FIND(" ",A2,1)>0,LEFT(A2,FIND(" ",A2,1)-1),0),A2))

    A sample file is also attached for your reference, hoipe it will help.

    Please let me know if it works for you.

     

    Thanks

    Tauqeer

     

3 Replies

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor

    mesmrc Hi

     

    Try below formula adjecant to main column:

     

    =PROPER(IFERROR(IF(FIND(" ",A2,1)>0,LEFT(A2,FIND(" ",A2,1)-1),0),A2))

    A sample file is also attached for your reference, hoipe it will help.

    Please let me know if it works for you.

     

    Thanks

    Tauqeer

     

Resources