# How to Extract & Count string from Text using VBA

#### footprints

##### New Member
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
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

##### New Member

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

##### Well-known Member
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

##### MrExcel MVP
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

#### footprints

##### New Member
Awesome, thanks a lot!

I really appreciate it.

#### erik.van.geit

##### MrExcel MVP
you're WELCOME!!

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

Replies
1
Views
1K
Replies
7
Views
295
Replies
1
Views
396
Replies
9
Views
3K
Replies
4
Views
4K

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.

### Which adblocker are you using?

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

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