#1   Report Post  
Ben
 
Posts: n/a
Default 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

  #2   Report Post  
Roger Govier
 
Posts: n/a
Default 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

  #3   Report Post  
Gary''s Student
 
Posts: n/a
Default 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


  #4   Report Post  
bpeltzer
 
Posts: n/a
Default 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


  #5   Report Post  
Ben
 
Posts: n/a
Default Text Formatting 2

You guys rock, thank you so much
Ben

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I stop Excel auto formatting the text 3-4 as 04 Apr? ahughf Excel Discussion (Misc queries) 3 October 16th 05 10:58 AM
grayscale conditional formatting of text Guenther Excel Discussion (Misc queries) 1 October 5th 05 01:16 PM
Filtering out text with conditional formatting bluebean Excel Discussion (Misc queries) 4 August 5th 05 04:43 PM
Formatting text to match Red/Amber/Green reports keith Excel Worksheet Functions 0 February 8th 05 12:06 AM
Read Text File into Excel Using VBA Willie T Excel Discussion (Misc queries) 13 January 8th 05 01:37 AM


All times are GMT +1. The time now is 05:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"