SOLVED

Conditional Name-Copying Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1967547%22%20slang%3D%22en-US%22%3EConditional%20Name-Copying%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1967547%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20of%20names%20that%20contains%20some%20cells%20that%20are%20just%20first%20name%2C%20and%20others%20that%20are%20first%2C%20last%2C%20and%20middle%2C%20all%20in%20one%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20have%20an%20adjacent%20column%20copy%20just%20the%20first%20name%20from%20the%20source%20column%20while%20leaving%20the%20source%20column%20unchanged%2C%20as%20well%20as%20capitalize%20(if%20not%20already%20capitalized)%20the%20first%20character%20of%20the%20copied%20first%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20compose%20a%20formula%20that%20can%20do%20this%3F%20The%20ones%20I've%20tried%20either%20require%20the%20source%20to%20have%20spaces%20(not%20applicable%20to%20the%20cells%20that%20are%20only%20first%20name%20currently)%2C%20or%20if%20I%20do%20Text-to-Columns%20it%20moves%20the%20source%20data%20rather%20than%20copying%20it%2C%20and%20I%20need%20the%20source%20column%20to%20remain%20unchanged.%20Really%20appreciate%20the%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1967547%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1967680%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Name-Copying%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1967680%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F894985%22%20target%3D%22_blank%22%3E%40mesmrc%3C%2FA%3E%26nbsp%3BHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20below%20formula%20adjecant%20to%20main%20column%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DPROPER(IFERROR(IF(FIND(%22%20%22%2CA2%2C1)%26gt%3B0%2CLEFT(A2%2CFIND(%22%20%22%2CA2%2C1)-1)%2C0)%2CA2))%3C%2FP%3E%3CP%3EA%20sample%20file%20is%20also%20attached%20for%20your%20reference%2C%20hoipe%20it%20will%20help.%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20it%20works%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1967697%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Name-Copying%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1967697%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3Bamazing%20thank%20you%20so%20much!%20Works%20perfectly%20%3Ahundred_points%3A%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!

3 Replies
Best Response confirmed by mesmrc (New Contributor)
Solution

@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

 

@tauqeeracma amazing thank you so much! Works perfectly

@mesmrc you are welcome