REmid function

[Bhupinder Rayat]

Hi All,

I am using the REmid function to pick out 13 character strings that end with
l, h, c or u.

e.g. cell A1 contains....
FIELD C2 @CHANGE ("PRPPRUNWECEOl") SIGN NOZERO;

=REMID(A1,"\b\w{12}(l|h|c|u)\b") returns PRPPRUNWECEOl

However, i also have 13 character strings that contain "$" and "£" i.e.
PRPGU$NY$CILl.

The REMID function above does not return these strings, what do I need to
add to the search criteria in the formula to search for "$"?

Any help will be much appreciated.

Thanks

Bhupinder.

 

[CLR]

I dunno about REMID, but this formula may serve you..........

=IF(OR(RIGHT(MID(A1,FIND(")",A1,1)-14,13),1)="l",RIGHT(MID(A1,FIND(")",A1,1)-14,13),1)="h",RIGHT(MID(A1,FIND(")",A1,1)-14,13),1)="c",RIGHT(MID(A1,FIND(")",A1,1)-14,13),1)="u"),MID(A1,FIND(")",A1,1)-14,13),"Does not meet criteria")

All on one line, watch out for word-wrap

hth
Vaya con Dios,
Chuck, CABGx3

 

[Bhupinder Rayat]

Hi CLR,

I tried a formula similar to yours and it doesn't always return what I want.
Also, your formula returns #VALUE as opposed to "Does not meet criteria" when
the IF conditions are not met.

Can anyone else solve my problem using REmid ?

Thanks anyway.

Bhupinder.

 

[Ron Rosenfeld]

Hi All,

I am using the REmid function to pick out 13 character strings that end with
l, h, c or u.

e.g. cell A1 contains....
FIELD C2 @CHANGE ("PRPPRUNWECEOl") SIGN NOZERO;

=REMID(A1,"\b\w{12}(l|h|c|u)\b") returns PRPPRUNWECEOl

However, i also have 13 character strings that contain "$" and "£" i.e.
PRPGU$NY$CILl.

The REMID function above does not return these strings, what do I need to
add to the search criteria in the formula to search for "$"?

Any help will be much appreciated.

Thanks

Bhupinder.

There is a wealth of information on constructing regular expressions. You can
look here for some basic information:
http://www.regular-expressions.info/reference.html

But note that REMID (assuming it is the routine I wrote) is a UDF which using
Microsoft VBScript Regular Expressions; so some of the information in the above
reference may not work. The below references may be of value for the VBScript
"flavor".

http://msdn.microsoft.com/library/d...html/2380d458-3366-402b-996c-9363906a7353.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb

In the Regular Expression you are using:

"\b\w{12}(l|h|c|u)\b"

the problem is that "\w" is shorthand for [A-Za-z0-9]. That character set,
obviously, does not include "$" and "£"

Depending on how specific you wish to be, you could explicitly include them, or
use a more general notation.

For example:
"\b[A-Za-z0-9$£]{12}(l|h|c|u)\b"

or, as a more general regex:

"\b\S{12}(l|h|c|u)\b"

"\S" means any non-whitespace character.

or even, if there cannot be numbers in the string

"\b\D{12}(l|h|c|u)\b"

"\D" means any non-digit character
--ron

 

[Bhupinder Rayat]

You are the man Ron!

Thank you ever so much, as you mentioned, I was puzzled by the "\w" which
threw me off course.

Regards,

Bhupinder

Hi All,

I am using the REmid function to pick out 13 character strings that end with
l, h, c or u.

e.g. cell A1 contains....
FIELD C2 @CHANGE ("PRPPRUNWECEOl") SIGN NOZERO;

=REMID(A1,"\b\w{12}(l|h|c|u)\b") returns PRPPRUNWECEOl

However, i also have 13 character strings that contain "$" and "£" i.e.
PRPGU$NY$CILl.

The REMID function above does not return these strings, what do I need to
add to the search criteria in the formula to search for "$"?

Any help will be much appreciated.

Thanks

Bhupinder.

There is a wealth of information on constructing regular expressions. You can
look here for some basic information:
http://www.regular-expressions.info/reference.html

But note that REMID (assuming it is the routine I wrote) is a UDF which using
Microsoft VBScript Regular Expressions; so some of the information in the above
reference may not work. The below references may be of value for the VBScript
"flavor".

http://msdn.microsoft.com/library/d...html/2380d458-3366-402b-996c-9363906a7353.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb

In the Regular Expression you are using:

"\b\w{12}(l|h|c|u)\b"

the problem is that "\w" is shorthand for [A-Za-z0-9]. That character set,
obviously, does not include "$" and "£"

Depending on how specific you wish to be, you could explicitly include them, or
use a more general notation.

For example:
"\b[A-Za-z0-9$£]{12}(l|h|c|u)\b"

or, as a more general regex:

"\b\S{12}(l|h|c|u)\b"

"\S" means any non-whitespace character.

or even, if there cannot be numbers in the string

"\b\D{12}(l|h|c|u)\b"

"\D" means any non-digit character
--ron

 

[Ron Rosenfeld]

You're welcome. Glad to help.

You are the man Ron!

Thank you ever so much, as you mentioned, I was puzzled by the "\w" which
threw me off course.

Regards,

Bhupinder

Hi All,

I am using the REmid function to pick out 13 character strings that end with
l, h, c or u.

e.g. cell A1 contains....
FIELD C2 @CHANGE ("PRPPRUNWECEOl") SIGN NOZERO;

=REMID(A1,"\b\w{12}(l|h|c|u)\b") returns PRPPRUNWECEOl

However, i also have 13 character strings that contain "$" and "£" i.e.
PRPGU$NY$CILl.

The REMID function above does not return these strings, what do I need to
add to the search criteria in the formula to search for "$"?

Any help will be much appreciated.

Thanks

Bhupinder.

There is a wealth of information on constructing regular expressions. You can
look here for some basic information:
http://www.regular-expressions.info/reference.html

But note that REMID (assuming it is the routine I wrote) is a UDF which using
Microsoft VBScript Regular Expressions; so some of the information in the above
reference may not work. The below references may be of value for the VBScript
"flavor".

http://msdn.microsoft.com/library/d...html/2380d458-3366-402b-996c-9363906a7353.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb

In the Regular Expression you are using:

"\b\w{12}(l|h|c|u)\b"

the problem is that "\w" is shorthand for [A-Za-z0-9]. That character set,
obviously, does not include "$" and "£"

Depending on how specific you wish to be, you could explicitly include them, or
use a more general notation.

For example:
"\b[A-Za-z0-9$£]{12}(l|h|c|u)\b"

or, as a more general regex:

"\b\S{12}(l|h|c|u)\b"

"\S" means any non-whitespace character.

or even, if there cannot be numbers in the string

"\b\D{12}(l|h|c|u)\b"

"\D" means any non-digit character
--ron

--ron