Why the command

b=Left(poz, InStr(poz, "!") + 1)

is working and

b=Left(poz, InStr(poz, "!") - 1)

doesn't work? It say: Invalid procedure call or argument...


--
mpele
------------------------------------------------------------------------
mpele's Profile: http://www.excelforum.com/member.php...o&userid=31808
View this thread: http://www.excelforum.com/showthread...hreadid=520041

Hi M,

If the exclamation character (!) is found, both expressions work for me.

If however, the character is not found, then the exptrssion:

InStr(poz, "!") - 1

equates to -1 which is an invalid in the Left function,


---
Regards,
Norman



"mpele" wrote in
message ...

Why the command

b=Left(poz, InStr(poz, "!") + 1)

is working and

b=Left(poz, InStr(poz, "!") - 1)

doesn't work? It say: Invalid procedure call or argument...


--
mpele
------------------------------------------------------------------------
mpele's Profile:
http://www.excelforum.com/member.php...o&userid=31808
View this thread: http://www.excelforum.com/showthread...hreadid=520041

If it doesn't find ! in the string poz, then the Instr returns 0. Subtract 1
from that, and you are then trying to get the leftmost -1 characters. Hence,
it is invalid.

You should test it

if InStr(poz, "!") 0 Then
b=Left(poz, InStr(poz, "!") - 1)
Else
b = poz
End If


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mpele" wrote in
message ...

Why the command

b=Left(poz, InStr(poz, "!") + 1)

is working and

b=Left(poz, InStr(poz, "!") - 1)

doesn't work? It say: Invalid procedure call or argument...


--
mpele
------------------------------------------------------------------------
mpele's Profile:
http://www.excelforum.com/member.php...o&userid=31808
View this thread: http://www.excelforum.com/showthread...hreadid=520041

Norman Jones Wrote:
Hi M,

If the exclamation character (!) is found, both expressions work for
me.

InStr gives result 6 but I need value 5 for function left.
What am I doing wrong? or what can I do wrong?!?


--
mpele
------------------------------------------------------------------------
mpele's Profile: http://www.excelforum.com/member.php...o&userid=31808
View this thread: http://www.excelforum.com/showthread...hreadid=520041

Hi M,

Your error is consistent with the exclamation character not being found.

If the expression:

InStr(poz, "!")

returns 6, as you suggest, I would not expect either of your original
expressions:

b=Left(poz, InStr(poz, "!") + 1)
or
b=Left(poz, InStr(poz, "!") - 1)

to produce your encountered error.


---
Regards,
Norman



"mpele" wrote in message
...

Norman Jones Wrote:
Hi M,

If the exclamation character (!) is found, both expressions work for
me.

InStr gives result 6 but I need value 5 for function left.
What am I doing wrong? or what can I do wrong?!?


--
mpele
------------------------------------------------------------------------
mpele's Profile:
http://www.excelforum.com/member.php...o&userid=31808
View this thread: http://www.excelforum.com/showthread...hreadid=520041