Left() Need the equivalent of this
 

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.

Left() Need the equivalent of this
 

One way:

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


In article ,
"Presto" wrote:

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.

Left() Need the equivalent of this
 

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" wrote:

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.

Left() Need the equivalent of this
 

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! :)


"Mike H" wrote in message
...
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" wrote:

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.

Left() Need the equivalent of this
 

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?


"Presto" wrote in message
...
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! :)


"Mike H" wrote in message
...
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" wrote:

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.

Left() Need the equivalent of this
 

On Wed, 3 Dec 2008 20:15:56 -0500, "Presto"
wrote:

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