Extract Alpha from A String and Compare with another String!

[aywen]

Hello...

I have a problems here. The problems is attached in the file. I wanna extract alpha/char from a string. Example: I wanna extract the words "(M)" with the bracket from the string "Toothbrush (M)" in column A. After extracting the (M) out, I wanna do a validation to compare the (M) in column B with another data in column C, if the (M) is same as the data called "Medium" in column c, the validation will return "Match" in the column d!

Can anyone help me out? Thanks you very much! Yr help is greatly appreciated!

The file can be downloaded from here:

http://h1.ripway.com/suaiko9999/test.xls

 

[jindon]

try
1) hit Alt + F11 to open vb editor
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 to get back to Excel

Function aywen(txt As String, ParamArray Ptn()) As String
Dim myPtn As String, e
For Each e In Ptn
     myPtn = myPtn & "|" & UCase(Left$(e,1))
Next
myPtn = "(\((" & Mid$(myPtn,2) & ")\))|(" & Join(Ptn,"|") & ")"
With CreateObject("VBScript.RegExp")
     .Pattern = myPtn
     .IgnoreCase = True
     aywen = StrConv(.execute(txt)(0),3)
End With
End Function

4) B2: =aywen(A2,"Hard","Medium","Soft")
5) C2: =If(Substitute(Substitute(Substitute(B2,"(H)","Hard"),"(M)","Medium"),"(S)","Soft")=C2,"Match","Mismatch")
edited:

 

[Scott Huish]

Maybe I'm missing something but why would C8 be Hard in the first place? Why not just extract the text or code for the Soft,Medium, and Hard and convert the code to what it's supposed to be and then you wouldn't have MATCH or MISMATCH, only a possibility that it doesn't contain that it doesn't contain the desired text or code.

 

[aywen]

Hello...thanks Jindon for your help. Really appreciate alot ya...eheheh!

But when I excute the function, I have this problem...

Function aywen(txt As String, ParamArry Ptn()) As String

the function cannot be run and the function is returning red. So is the function have problems? Looks like "ParamArry Ptn()" has problem. Thanks![/quote]

 

[jindon]

OOps

Should reas as

ParamArray

 

[aywen]

Thanks...the problems is solve and I can run the function now. I got the results I wanted as well. Thanks for yr help. Really appreciate your help. You are a genius =)

I just found out I have one condition to solve as well.

In the records C9, I wanna have a results of "N/A" in that C9 instead of "#VALUE!". And I wish to have the results of "Mismatch" in D9 as well. Thanks! How to change the #VALUE! to other meaningful naming?? Sorry to trouble u =)

Please have a look in the file here.

http://h1.ripway.com/aywen/test.xls

 

[jindon]

try to change to formula in D2

=If(And(Not(IsError(B2)),Substitute(Sbustitute(Substitute("(H)","Hard"),"(M)","Medium"),"(S)","Soft")=C2),"Match","Mismatch")

 

[aywen]

Hey...i tried the code...looks like there is a problem there.

 

[jindon]

OOps

=If(And(Not(IsError(B2)),Substitute(Sbustitute(Substitute(B2,"(H)","Hard"),"(M)","Medium"),"(S)","Soft")=C2),"Match","Mismatch")

 

[jindon]

And also you need to replace the function with the following code.

Function aywen(txt As String, ParamArray Ptn()) As String
Dim myPtn As String, e
For Each e In Ptn
     myPtn = myPtn & "|" & UCase(Left$(e,1))
Next
myPtn = "(\((" & Mid$(myPtn,2) & ")\))|(" & Join(Ptn,"|") & ")"
With CreateObject("VBScript.RegExp")
     .Pattern = myPtn
     .IgnoreCase = True
     If Len(.execute(txt)(0))>1 Then
          aywen = StrConv(.execute(txt)(0),3)
     Else
          aywen = UCase(.execute(txt)(0))
     End If
End With
End Function