IF(ISERROR...won't return first value which is a cell reference

[blini]

Hello,

I have to clean up a dataset of names by removing a middle name if it has been included with the firstname.

So, I'm using the following formula to look for a space, return the cell itself if there is no space, or just the first word if there is a space.

I know the formula is in German, so that is why I have given the English version underneath. (My German isn't good enough to write the question.)

=WENN(NICHT(FINDEN(" ";B3;1));B3;LINKS(B3;FINDEN(" ";B3;1)-1))

= IF(ISERROR(FIND(" ",B3,1)),B3,LEFT(B3,FIND(" ",B3,1)-1))


The formula works if there is a second name in the cell, but returns a #NO VALUE! (#WERT!) error if there is just one name by itself.

How can I get the cell value to be returned when there is just one name present in the cell?

 

[Comfy]

Your formula already does this.

If your cell contains a space it will return all characters up to the space.
If your cell does not contain a space it will return the cell value.

What value does your cell contain that gives you an error?

Excel 2010
	A	B
1	John	John Doe
2	John	John
Sheet1

Cell Formulas
Range	Formula
A1	= IF(ISERROR(FIND(" ",B1,1)),B1,LEFT(B1,FIND(" ",B1,1)-1))

 

[blini]

Ah, I got the wrong German word for ISERROR, it should be ISTFEHLER. The German NICHT equates to NOT.

Thanks for the confirmation that the formula was correct in English.

Problem solved