Extract Alpha from A String and Compare with another String!

aywen

New Member
Joined
Aug 16, 2007
Messages
11
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
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
     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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Aug 16, 2007
Messages
11
Hello...thanks Jindon for your help. Really appreciate alot ya...eheheh!

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

Code:
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

OOps

Should reas as

ParamArray
 

aywen

New Member
Joined
Aug 16, 2007
Messages
11
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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

New Member
Joined
Aug 16, 2007
Messages
11
Hey...i tried the code...looks like there is a problem there.

test.JPG
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
OOps

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

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
And also you need to replace the function with the following code.
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,884
Members
414,029
Latest member
mrwilker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top