How to Extract & Count string from Text using VBA

footprints

New Member
Joined
Jul 1, 2008
Messages
3
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
Welcome to the Board!

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

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

Hope that helps.
 
Upvote 0

footprints

New Member
Joined
Jul 1, 2008
Messages
3
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
 
Upvote 0

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 
Upvote 0

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

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

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

Forum statistics

Threads
1,191,076
Messages
5,984,495
Members
439,893
Latest member
johnsboxftm

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