Left() Need the equivalent of this

[Presto]

In Access 2003 to get a word before the " / " from Smith/Jones you use
the following code:

IIf([Team1] Is Null,"",Left([Team1],InStr([Team1],"/")-1))

What would the equivalent of this in an Excel spreadsheet? I want to have
the field blank if there is no data in the field.

 

[JE McGimpsey]

One way:

=IF(A1="", "", LEFT(A1 ,FIND("/", A1) - 1))

 

[Mike H]

Hi,

I don't know of any circumstance in which your formula would return the
first word and am not sure if you mean Vb or worksheet so here's both

vb
mystring = Left(Range("A1"), InStr(Range("A1"), "/") - 1)

worksheet
=LEFT(A1,FIND("/",A1)-1)

Mike

 

[Presto]

Actually , it's really cool that you posted both. Kudos!

However, Mr McGimpsey included the If statement which is exactly what I
needed.... so he wins!

Thanks to you both. You pointed me in the right direction!

Hi,

I don't know of any circumstance in which your formula would return the
first word and am not sure if you mean Vb or worksheet so here's both

vb
mystring = Left(Range("A1"), InStr(Range("A1"), "/") - 1)

worksheet
=LEFT(A1,FIND("/",A1)-1)

Mike

In Access 2003 to get a word before the " / " from Smith/Jones you
use
the following code:

IIf([Team1] Is Null,"",Left([Team1],InStr([Team1],"/")-1))

What would the equivalent of this in an Excel spreadsheet? I want to have
the field blank if there is no data in the field.

 

[Presto]

OK, I'm stuck again.
I was trying to use the same logic for MID, to get everything AFTER the /
but it's not working. I only get the / as a result.
I'm using : =IF(E3="", "", MID(E3,FIND("/", E3),1))

=FIND("/",E3) will result in the position of the slash - I understand that
.....
Whats wrong with this picture?

Actually , it's really cool that you posted both. Kudos!

However, Mr McGimpsey included the If statement which is exactly what I
needed.... so he wins!

Thanks to you both. You pointed me in the right direction!

Hi,

I don't know of any circumstance in which your formula would return the
first word and am not sure if you mean Vb or worksheet so here's both

vb
mystring = Left(Range("A1"), InStr(Range("A1"), "/") - 1)

worksheet
=LEFT(A1,FIND("/",A1)-1)

Mike

In Access 2003 to get a word before the " / " from Smith/Jones you
use
the following code:

IIf([Team1] Is Null,"",Left([Team1],InStr([Team1],"/")-1))

What would the equivalent of this in an Excel spreadsheet? I want to
have
the field blank if there is no data in the field.

 

[Ron Rosenfeld]

OK, I'm stuck again.
I was trying to use the same logic for MID, to get everything AFTER the /
but it's not working. I only get the / as a result.
I'm using : =IF(E3="", "", MID(E3,FIND("/", E3),1))

=FIND("/",E3) will result in the position of the slash - I understand that
....
Whats wrong with this picture?

You are only returning 1 character.
Look at HELP for the MID function, or check out the prompts as you enter the
formula:

Try:

=IF(E3="", "", MID(E3,FIND("/", E3)+1,255))

The +1 tells the MID function to start returning characters at the position
that is 1 after the "/". The 255 needs to be some value greater than the
length of the rest of the string.
--ron