| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Nesting 2 If functions
Hello everyone, would really appreciate some help with this - am pulling my hair out at the moment, and I don't have much left to pull! thx How can I combine these 2 formulae into 1, and so that they will return the name if it doesn't have a space or hyphen? Cell D3 contains surnames. I want to use this cell to create passwords in another column by either using the whole surname or a portion of the surname if it contains hyphens and spaces. 1st formula removes hyphens succesfully although it doesn't return the value if false IF(SEARCH("-", D3, 1)=1, MID(D3,1,FIND("-",D3,1)-1), D3) 2nd formula removes the space but it doesn't return the value if valse. IF(SEARCH(" ", D3, 1)=1, MID(D3,1,FIND(" ",D3,1)-1), D3) e.g D3 is Smith-Jones: Formula 1 gives me Smith D3 is Smith Jones: Formula 2 gives me Smith D3 is Smithjones: both formulae give me #VALUE! -- TMinterPosted from http://www.pcreview.co.uk/ newsgroup access |
|
#2
|
|||
|
|||
|
=SUBSTITUTE(SUBSTITUTE(D3,"-","")," ","")
-- HTH Bob Phillips "TMinter" <TMinter.1rfu3y@ wrote in message ... Hello everyone, would really appreciate some help with this - am pulling my hair out at the moment, and I don't have much left to pull! thx How can I combine these 2 formulae into 1, and so that they will return the name if it doesn't have a space or hyphen? Cell D3 contains surnames. I want to use this cell to create passwords in another column by either using the whole surname or a portion of the surname if it contains hyphens and spaces. 1st formula removes hyphens succesfully although it doesn't return the value if false IF(SEARCH("-", D3, 1)=1, MID(D3,1,FIND("-",D3,1)-1), D3) 2nd formula removes the space but it doesn't return the value if valse. IF(SEARCH(" ", D3, 1)=1, MID(D3,1,FIND(" ",D3,1)-1), D3) e.g D3 is Smith-Jones: Formula 1 gives me Smith D3 is Smith Jones: Formula 2 gives me Smith D3 is Smithjones: both formulae give me #VALUE! -- TMinterPosted from http://www.pcreview.co.uk/ newsgroup access |
|
#3
|
|||
|
|||
|
try instead
=Substitute(Substitute(D3," ",""),"-","") "TMinter" wrote: Hello everyone, would really appreciate some help with this - am pulling my hair out at the moment, and I don't have much left to pull! thx How can I combine these 2 formulae into 1, and so that they will return the name if it doesn't have a space or hyphen? Cell D3 contains surnames. I want to use this cell to create passwords in another column by either using the whole surname or a portion of the surname if it contains hyphens and spaces. 1st formula removes hyphens succesfully although it doesn't return the value if false IF(SEARCH("-", D3, 1)=1, MID(D3,1,FIND("-",D3,1)-1), D3) 2nd formula removes the space but it doesn't return the value if valse. IF(SEARCH(" ", D3, 1)=1, MID(D3,1,FIND(" ",D3,1)-1), D3) e.g D3 is Smith-Jones: Formula 1 gives me Smith D3 is Smith Jones: Formula 2 gives me Smith D3 is Smithjones: both formulae give me #VALUE! -- TMinterPosted from http://www.pcreview.co.uk/ newsgroup access |
|
#4
|
|||
|
|||
|
Perfect! A very neat solution. thank you -- TMinterPosted from http://www.pcreview.co.uk/ newsgroup access |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nesting functions in the functions dialog box | Excel Worksheet Functions | |||
| nesting functions | Excel Worksheet Functions | |||
| Nesting Functions | Excel Worksheet Functions | |||
| What is the syntax for nesting functions such as IF AND? | Excel Worksheet Functions | |||
| nesting 18 x functions | Excel Worksheet Functions | |||
Linear Mode
