Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Text Formatting 2
You guys rock, thank you so much
Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop Excel auto formatting the text 3-4 as 04 Apr? | Excel Discussion (Misc queries) | |||
grayscale conditional formatting of text | Excel Discussion (Misc queries) | |||
Filtering out text with conditional formatting | Excel Discussion (Misc queries) | |||
Formatting text to match Red/Amber/Green reports | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |