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

=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

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

Perfect! A very neat solution.

thank you


--
TMinterPosted from http://www.pcreview.co.uk/ newsgroup access