How to consider * at the end of string as only a character rather than a wildcard character?

Amit1

New Member
Joined
Jul 6, 2012
Messages
23
Dear Friends,
I am having a question regarding macro development with "*" character.
I am trying to develop a macro where i have to sort , count and remove the duplicates for some strings(Each string is existing in a cell).But few strings contains "*" character at the end.
While doing the operations this character is getting considered as a wild card entry which is messing up the counts.
How to declare and write the code in VBA
so that this last "*" character will be considered as only a character rather than wildcard entry.


Some example of the strings in the column are as follows:
aa-bb-cccd
ba-cc-ad
abcird
cca-dc-it*
vva-tt-pq*

Need your help at your earliest convenience.

Best regards
Amit
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
While doing the operations this character is getting considered as a wild card entry which is messing up the counts.

Post this code.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I think drsarao meant post your code.

To escape a wildcard character precede it with a tilde (~).
 

Amit1

New Member
Joined
Jul 6, 2012
Messages
23

ADVERTISEMENT

Dear Andrew,
Thanks for the suggestion.The primary macro I have generated is as follow:
Code:
Sub Test()
'
' Test Macro
' test macro for sorting, count and duplicate removal for string data ending with *
'
' Keyboard Shortcut: Ctrl+p
'
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A8"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:A8")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-1]:R[6]C[-1],RC[-1])"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B8")
    Range("B2:B8").Select
    Range("A2:B8").Select
    ActiveSheet.Range("$A$2:$B$8").RemoveDuplicates Columns:=2, Header:=xlNo
End Sub
This is yielding wrong result.Though I have to cross check for any alternative logic,
could you please let me know how to precede the * with tilde (`) in the code.
I have tried to record a macro with the suggested point but not able to get it.
Waiting for your valuable suggestion.

Best Regards,
Amit
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use this formula:

=COUNTIF(C[-1],SUBSTITUTE(RC[-1],"*","~*"))

In VBA it would be:

Code:
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],SUBSTITUTE(RC[-1],""*"",""~*""))"
 

Amit1

New Member
Joined
Jul 6, 2012
Messages
23
Dear Andrew,
Thanks a lot for the suggestion. I will try it out.

Best Regards,
Amit
 

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

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