How to Extract & Count string from Text using VBA

[footprints]

Hi,

Using VBA, I'm trying to count how many times the following strings appear on the the report:
BTC, WD, ABC, XYZ

The challenge is the cell may contain both 'stand alone' or combination of those characters..so something like this:
- BTC, Wd, abcblablaba, xyz
- btC, wd
- klm, SC, WD
- blbalbalXYZ
- blablabaxyz
- xyz,abc, btc
- XYT, XYZ, BTC

In this example, the total count are:
BTC: 4
WD: 3
ABC: 2
XYZ: 5


So whether it's standalone or combination, mixed upper/lowercase, spaces in between... I just want to get a count of how many times that those specific character appear, i.e:
- blbalbalXYZ -> count as 1 XYZ
- xyz -> count as 1 XYZ
- ...., xyz -> count as 1 XYZ

so doesn't matter how/what/where the combination are, as long as that specific character appears, then it gets counted.

 

[schielrn]

Welcome to the Board!

Why do you need vba? Can you just use a formula:

=SUMPRODUCT(--ISNUMBER(SEARCH("BTC",A1:A1000)))

Hope that helps.

 

[footprints]

Thanks for your reply!

I guess using the formula you showed is not 100% capturing all the combination of the text, for example the following list:

abcbtc
blablabtc
abcbtc, GBTC, BTC

There are 5 BTC in there but using the formula it only captures 3 of them.

So basically, what I wanna do is count the number of occurences of a particular text case using VBA function. This function will evaluate a selected range and return the number of occurences of all UPPER, lower, MiXeD case text strings, as well as skipping over empty cells, dates, and formulas.
And later I want to put a button so the user can just click on it to get a summary of all the counts.

Any advice would be appreciate it.

thanks

 

[MrKowz]

I can't help you with the script, but as far as the button goes, create a button on the worksheet and assign it to run a macro in the VBA editor.

Example:

Private Sub CommandButton1_Click()
macroname
End Sub

 

[erik.van.geit]

Hi,

While being a code lover, I would still suggest formulas to do this.
Any VBA solution could use these formulas combined with "evaluate".

  A                         B  C     D              E      F          
1 LIST                         ITEMS case sensitive not cs # of cells 
2 BTC, Wd, abcblablaba, xyz    BTC   3              7      4          
3 btC, wd                      WD    1              3      3          
4 klm, SC, WD                  ABC   1              2      2          
5 blbalbalXYZ                  XYZ   2              5      5          
6 blablabaxyz                                                         
7 xyz,ABC, btc                                                        
8 XYT, XYZ, BTC btc BTC BTc                                           
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
D2:D5 =SUMPRODUCT(LEN($A$2:$A$8)-LEN(SUBSTITUTE($A$2:$A$8,C2,"")))/LEN(C2)
E2:E5 =SUMPRODUCT(LEN($A$2:$A$8)-LEN(SUBSTITUTE(UPPER($A$2:$A$8),UPPER(C2),"")))/LEN(C2)
F2:F5 =COUNTIF($A$2:$A$8,"*"&C2&"*")
[Table-It] version 09 by Erik Van Geit

kind regards,
Erik

 

[footprints]

Awesome, thanks a lot!

I really appreciate it.

 

[erik.van.geit]

you're WELCOME!!

and ... by the way ...
WELCOME to the BOARD!!!!!