MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Trimmimg text

Posted by David Williams on July 28, 2001 6:00 AM

I have a column of names some with 1 name some with 2
ie Jane and Jane Alice
I want to trim this in another column to only use the first name. I've tried left(c1,find(" ",c1)) but this returns an error if there is no space. I've tried using IF to test for a space but that returns errors.
I can do this easily on another spreadsheet I use.


Posted by cpod on July 28, 2001 6:58 AM

=IF(ISERR(FIND(" ",C1)),C1,LEFT(C1,FIND(" ",C1)-1))

Posted by David Williams on July 29, 2001 12:04 AM

Thanks, I'd not thought to look for an error function. My other spreadsheet doesn't use them.

Posted by Aladin Akyurek on July 29, 2001 5:18 AM

You can also use another formula that does not use ISERR but has the same effect as cpod's.

=IF(ISNUMBER(FIND(" ",C1)),LEFT(C1,FIND(" ",C1)-1),C1)

BTW: FIND is case-sensitive. If that feature is not needed, use SEARCH instead.