Text Formatting 2
 

I have a spreadsheet with 2 versions of text in 1 columm

1. 1 column = firstname and initial. For example Dave C.
2. 1 column = firstname. For example Dave

DaveO kindly provided this suggestion:

=MID(D7,1,FIND(" ",D7,1)-1)
where D7 contains the name in question. This formula returns
everything before the first blank space in the entry

This works perfectly for situation number 1, but in situation 2 I get a
#VALUE! error.

How can I mask the cell to be just the first name in both scenarios?

Thanks

Ben

Text Formatting 2
 

Hi Ben

One way
=IF(ISERROR(FIND(" ",D7,1)),D7,MID(D7,1,FIND(" ",D7,1)-1))

Regards

Roger Govier


Ben wrote:
I have a spreadsheet with 2 versions of text in 1 columm

1. 1 column = firstname and initial. For example Dave C.
2. 1 column = firstname. For example Dave

DaveO kindly provided this suggestion:

=MID(D7,1,FIND(" ",D7,1)-1)
where D7 contains the name in question. This formula returns
everything before the first blank space in the entry

This works perfectly for situation number 1, but in situation 2 I get a
#VALUE! error.

How can I mask the cell to be just the first name in both scenarios?

Thanks

Ben

Text Formatting 2
 

DaveO's formula can easily be modified to handle on one name case:

=IF(ISERR(MID(D7,1,FIND(" ",D7,1)-1)),D7,MID(D7,1,FIND(" ",D7,1)-1))

Which is just saying that if the Dave formula raises an error, use the data
as is. If the Dave formula does not raise an error, then use it!
--
Gary's Student


"Ben" wrote:

I have a spreadsheet with 2 versions of text in 1 columm

1. 1 column = firstname and initial. For example Dave C.
2. 1 column = firstname. For example Dave

DaveO kindly provided this suggestion:

=MID(D7,1,FIND(" ",D7,1)-1)
where D7 contains the name in question. This formula returns
everything before the first blank space in the entry

This works perfectly for situation number 1, but in situation 2 I get a
#VALUE! error.

How can I mask the cell to be just the first name in both scenarios?

Thanks

Ben

Text Formatting 2
 

Put the formula inside an IF function, so that if there is a space, you keep
the characters up to that space; if there is no space you just take what you
were given:
=if(isnumber(find(" ",d7)),left(d7,find(" ",d7)-1,d7)
--Bruce

"Ben" wrote:

I have a spreadsheet with 2 versions of text in 1 columm

1. 1 column = firstname and initial. For example Dave C.
2. 1 column = firstname. For example Dave

DaveO kindly provided this suggestion:

=MID(D7,1,FIND(" ",D7,1)-1)
where D7 contains the name in question. This formula returns
everything before the first blank space in the entry

This works perfectly for situation number 1, but in situation 2 I get a
#VALUE! error.

How can I mask the cell to be just the first name in both scenarios?

Thanks

Ben

Text Formatting 2
 

You guys rock, thank you so much
Ben