|
REmid function
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. |
REmid function
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" wrote: 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. |
REmid function
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. "CLR" wrote: 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" wrote: 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. |
REmid function
On Mon, 25 Sep 2006 07:46:01 -0700, Bhupinder Rayat
wrote: 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/de...63906a7353.asp http://support.microsoft.com/default...02&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 |
REmid function
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 "Ron Rosenfeld" wrote: On Mon, 25 Sep 2006 07:46:01 -0700, Bhupinder Rayat wrote: 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/de...63906a7353.asp http://support.microsoft.com/default...02&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 |
REmid function
You're welcome. Glad to help.
On Thu, 28 Sep 2006 02:39:01 -0700, Bhupinder Rayat wrote: 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 "Ron Rosenfeld" wrote: On Mon, 25 Sep 2006 07:46:01 -0700, Bhupinder Rayat wrote: 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/de...63906a7353.asp http://support.microsoft.com/default...02&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 |
| All times are GMT +1. The time now is 08:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com