VB Replace number with text to another number

Darzl

New Member
Joined
Feb 16, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi All,

New to this forum because I can't find the information I require :)

I have a workbook with several sheets, I have a data sheet and a sheet that collects from the data sheet and give information using COUNTIF functions

The data part is added by a few users and I used to have it in Google sheets where it worked perfectly but because I use * function to search MS excel does not see numbers in the search and count.

So I have been looking at doing an automatic change using VB instead of having to do a find and replace every time data is added.

I have 4 items I wish to change in the same column H:H, If the loaded data is 22HG1 or 220G1 for instance I want it to be changed to 2 then this =COUNTIFS(January_Data!$C:$C,"08/01/2021",January_Data!$H:$H,"2") will work where before is was =COUNTIFS(January_Data!$C:$C,"08/01/2021",January_Data!$H:$H,"2*") and the first one would give a value of 500 and the actual would be 700

I have tried a few VBA scripts I found online but nothing seems to work.

2020090100123000819/1/202012:46:52 AM22G1
2020090100123000939/1/202012:49:42 AM22G0
2020090100123000949/1/202012:49:42 AM22G1
2020090100123000999/1/202012:59:10 AM22G1
2020090100123001009/1/202012:59:10 AM22G1
202009010012300104408201853269/1/20201:03:25 AM22G1
2020090100123001089/1/20201:01:15 AM22G1
202009010012300115408201559689/1/20201:09:37 AM2210
202009010012300116408201559689/1/20201:09:37 AM2210
202009010012300127408201559689/1/20201:24:13 AM2210
202009010012300128408201559689/1/20201:24:13 AM2210
202009010012300129408201756489/1/20201:25:03 AM2210
202009010012300130408201756489/1/20201:25:03 AM2210
202009010012300133408201853269/1/20201:25:17 AM22G1
202009010012300134408201853269/1/20201:25:17 AM22G1
202009010012300139408201787519/1/20201:27:03 AM22G1
202009010012300140408201787519/1/20201:27:03 AM22G1
202009010012300153408201754359/1/20201:32:02 AM2210
202009010012300154408201754359/1/20201:32:02 AM2210
2020090100123001929/1/20202:39:36 AM22G1
2020090100123002029/1/20202:38:43 AM22G1
202009010012300204M19000062889/1/20202:47:57 AM22G1
202009010012300205408201416589/1/20202:48:11 AM2210
202009010012300206408201416589/1/20202:48:11 AM2210
202009010012300305408201842389/1/20205:57:24 AM22G1
202009010012300306408201842389/1/20205:57:24 AM22G1
202009010012300311408201600729/1/20205:59:45 AM22G1
202009010012300312408201600729/1/20205:59:45 AM22G1
202009010012300325408201853269/1/20206:14:26 AM22G1
202009010012300326408201853269/1/20206:14:26 AM22G1
202009010012300328408201839489/1/20206:07:35 AM45G1
202009010012300369908201820299/1/20208:09:46 AM2210
202009010012300370908201820299/1/20208:09:46 AM2210
202009010012300375408201416589/1/20208:19:49 AM2210
202009010012300376408201416589/1/20208:19:49 AM2210
202009010012300389408201600729/1/202010:29:23 AM22G1
202009010012300390408201600729/1/202010:29:23 AM22G1
202009010012300399708201424199/1/20208:02:36 AM2210
202009010012300400708201424199/1/20208:02:36 AM2210
202009010012300401408201600729/1/20208:03:12 AM22G1
202009010012300402408201600729/1/20208:03:12 AM22G1
2020090100123004129/1/20207:47:02 AM2210
202009010012300445408201810649/1/202011:47:02 AM2210
202009010012300446408201810649/1/202011:47:02 AM2210
202009010012300449408201416589/1/202011:54:34 AM2210
202009010012300450408201416589/1/202011:54:34 AM2210
202009010012300461407201274069/1/202012:07:40 PM22G1
202009010012300462407201274069/1/202012:07:40 PM22G1
202009010012300473408201545979/1/202012:11:14 PM22G1
202009010012300474408201545979/1/202012:11:14 PM22G1
2020090100123005109/1/202012:53:04 PM22G1
202009010012300548408201427009/1/20201:17:41 PM45G0
202009010012300556708201359679/1/20201:19:47 PM22G1
202009010012300566408201554399/1/20201:22:50 PM45G1
202009010012300573408201644299/1/20201:18:44 PM22G1
202009010012300578708201546029/1/20201:25:01 PM22G1
202009010012300584708201546029/1/20201:25:01 PM22G1
202009010012300600408201555309/1/20201:29:39 PM4510
2020090100123006279/1/20202:13:29 PM22G1
2020090100123006479/1/20202:42:00 PM22G1
2020090100123006489/1/20202:42:00 PM22G1
202009010012300651708201546029/1/20202:43:05 PM22G1
202009010012300652708201546029/1/20202:43:05 PM22G1
2020090100123006539/1/20202:44:54 PM22G1
2020090100123006549/1/20202:44:54 PM22G1
2020090100123006699/1/20203:00:56 PM22G1
2020090100123006709/1/20203:00:56 PM22G1
2020090100123006729/1/20203:01:57 PM22G1
2020090100123006739/1/20203:03:01 PM2210
2020090100123006749/1/20203:03:01 PM22G1
2020090100123006809/1/20203:10:36 PM22G1
2020090100123006819/1/20203:10:36 PM22G1
202009010012300685408201816639/1/20203:05:43 PM22G1
202009010012300686408201816639/1/20203:05:43 PM22G1
202009010012300689408201496849/1/20203:29:20 PM22G1
202009010012300690408201496849/1/20203:29:20 PM22G1
2020090100123006999/1/20203:40:03 PM22G1
2020090100123007029/1/20203:40:03 PM22G1
202009010012300709408201807299/1/20203:46:43 PM22G1
202009010012300710408201807299/1/20203:46:43 PM22G1
202009010012300717708201359679/1/20203:51:35 PM22G1
202009010012300718708201359679/1/20203:51:35 PM22G1
202009010012300729408201600729/1/20204:00:57 PM22G1
202009010012300730408201600729/1/20204:00:57 PM22G1
202009010012300737408201816639/1/20204:04:21 PM22G1
202009010012300738408201816639/1/20204:04:21 PM22G1
2020090100123007399/1/20203:47:54 PM2210
2020090100123007409/1/20203:47:54 PM2210
2020090100123007439/1/20204:10:45 PM22G1
2020090100123007449/1/20204:10:45 PM22G1
2020090100123007559/1/20204:12:40 PM22G1
2020090100123007569/1/20204:12:40 PM22G1
2020090100123007939/1/20204:47:28 PM22G1
2020090100123007949/1/20204:47:28 PM22G1
2020090100123008119/1/20204:51:46 PM22G1
202009010012300819408201787519/1/20205:00:18 PM22G1
202009010012300820408201787519/1/20205:00:18 PM22G1
2020090100123008219/1/20205:03:21 PM22G1
2020090100123008229/1/20205:03:21 PM22G1
202009010012300829708201764119/1/20205:10:16 PM22G1
202009010012300830708201764119/1/20205:10:16 PM22G1
202009010012300835408201600729/1/20205:13:53 PM22G1
202009010012300837408201787519/1/20205:16:48 PM22G1
202009010012300838408201787519/1/20205:16:48 PM22G1
202009010012300839408201600729/1/20205:18:23 PM22G1
202009010012300840408201600729/1/20205:18:23 PM22G1
202009010012300845408201787519/1/20205:22:55 PM22G1
202009010012300846408201787519/1/20205:22:55 PM22G1
202009010012300857408201842669/1/20205:29:25 PM2210
202009010012300858408201842669/1/20205:29:25 PM2210
202009010012300895408201842649/1/20205:39:11 PM2210
202009010012300896408201842649/1/20205:39:11 PM2210
202009010012300897408201496849/1/20205:40:18 PM22G1
202009010012300898408201496849/1/20205:40:18 PM22G1
202009010012300903708201359679/1/20205:42:48 PM22G1
202009010012300904708201359679/1/20205:42:48 PM22G1
202009010012300927408201339739/1/20206:11:57 PM22G1
202009010012300928408201339739/1/20206:11:57 PM22G1
202009010012300931408201787519/1/20206:14:46 PM22G1
202009010012300932408201787519/1/20206:14:46 PM22G1
202009010012300967408201624479/1/20207:42:33 PM22G1
202009010012300968408201624479/1/20207:42:33 PM22G1
202009010012300969408201842649/1/20207:43:29 PM2210
202009010012300970408201842649/1/20207:43:29 PM2210
202009010012300974408201842509/1/20207:46:17 PM2210
202009010012300975408201842509/1/20207:46:17 PM2210
202009010012300977408201633539/1/20207:45:07 PM2210
202009010012300978408201633539/1/20207:45:07 PM2210
202009010012300985708201424199/1/20207:52:09 PM2210
202009010012300986708201424199/1/20207:52:09 PM2210
202009010012300988708201606459/1/20207:52:38 PM22G1
202009010012300990408201845009/1/20207:53:27 PM45G1
202009010012301003408201339739/1/20208:18:36 PM22G1
202009010012301004408201339739/1/20208:18:36 PM22G1
202009010012301005408201496809/1/20208:19:38 PM22G1
202009010012301006408201496809/1/20208:19:38 PM22G1
202009010012301007408201842509/1/20208:20:38 PM2210
202009010012301008408201842509/1/20208:20:38 PM2210
202009010012301015408201559689/1/20208:21:50 PM2210
202009010012301016408201559689/1/20208:21:50 PM2210
202009010012301029408201600729/1/20208:42:47 PM22G1
202009010012301030408201600729/1/20208:42:47 PM22G1
202009010012301035408201496809/1/20208:54:20 PM22G1
202009010012301036408201496809/1/20208:54:20 PM22G1
202009010012301041408201842509/1/20208:53:12 PM2210
202009010012301042408201842509/1/20208:53:12 PM2210
202009010012301045408201496809/1/20208:59:44 PM22G1
202009010012301046408201496809/1/20208:59:44 PM22G1
202009010012301047408201842509/1/20208:56:04 PM2210
202009010012301048408201842509/1/20208:56:04 PM2210
202009010012301049408201339739/1/20209:01:40 PM22G1
202009010012301050408201339739/1/20209:01:40 PM22G1

Thanks in advance

D
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,148
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

If I'm interpreting the question correctly, you're asking about converting strings like 22HG1 or 220G1 to 2, so that you can use a COUNTIFS( ___, 2) to count them?

That's not necessary - you can count directly. Are you looking to count everything in H where the first character is 2?
 

Darzl

New Member
Joined
Feb 16, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi Stephen,

Thank you :)

How can I do the count the way it is at the moment? Yes I was looking to automatically change anything with 2**** to 2 and 4**** to 4 and so on because in the COUNTIF formula 2* or 4* will not count correctly if the character after the 2 or 4 is a number and not a letter.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,148
Office Version
  1. 365
Platform
  1. Windows
Perhaps like this:

ABCDEFGH
1
2Match9 Jan 20209 Jan 202022G1
329 Jan 20202xxx
4Result119 Jan 202045G1
59 Jan 202022G1
69 Jan 202022G1
79 Jan 202045G1
89 Jan 20202xxx
99 Jan 20202210
109 Jan 202023yy
119 Jan 202045G1
129 Jan 20202210
139 Jan 20202210
149 Jan 20202210
159 Jan 202022G1
1610 Jan 202122G1
1710 Jan 202122G1
1810 Jan 202122G1
1910 Jan 202145G1
Sheet2
Cell Formulas
RangeFormula
C4C4=SUMPRODUCT((E2:E25=C2)*(LEFT(H2:H25,LEN(C3))=C3&""))
 

Darzl

New Member
Joined
Feb 16, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Looks good, I will give it a try, so it looks like I just have to add the 1, 4 in field to aid the search.

Thank you
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,148
Office Version
  1. 365
Platform
  1. Windows
... so it looks like I just have to add the 1, 4 in field to aid the search.
Correct, just set C3 appropriately.

The formula also adjusts for the length of the search string, e.g. if you wanted to count all 221* or 22G*, rather than just 2*
 

Darzl

New Member
Joined
Feb 16, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I managed to get it to work by the looks of it, can I use the same SUMPRODUCT to do a similar search? I have a column in the same sheet that has a Red and column be has the numbers beginning with 4, I am not worried about the date Just the total of 4s that have 22 for instance.

Thanks
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,148
Office Version
  1. 365
Platform
  1. Windows
Yes, you should be be able to adapt the technique.

Why not give it a try, and post back if you can't make it work.
 

Darzl

New Member
Joined
Feb 16, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I did a search on the net and found something with -- and it didn't work because I put * between, changed to -- and it seems to work

=SUMPRODUCT(--(January_Data!E:E=$I$44)--(January_Data!B:B=$D$1&"")) I44 = Red and D1 = 4
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,148
Office Version
  1. 365
Platform
  1. Windows
I did a search on the net and found something with -- and it didn't work because I put * between, changed to -- and it seems to work

=SUMPRODUCT(--(January_Data!E:E=$I$44)--(January_Data!B:B=$D$1&"")) I44 = Red and D1 = 4
The general form of this type of count is

=SUMPRODUCT(TestA,TestB,TestC ...)

Each test, e.g. January_Data!E:E=$I$44, returns a vector of boolean values TRUE or FALSE. The SUMPRODUCT works on numbers, so we need to coerce these boolean values into 1s and 0s. The usual and most efficient way to do this is to use the unary (double negative) operator --, because --TRUE evaluates as 1, and hence will be counted by the SUMPRODUCT. Other ways you see from time to time include adding 0, or multiplying by 1 (as TRUE+0 = 1, and 1*TRUE=1).

My formula coerced the boolean values by multiplying them (TRUE*TRUE=1).

So you could use:

=SUMPRODUCT(--(January_Data!E:E=$I$44),--(January_Data!B:B=$D$1&""))
or
=SUMPRODUCT((January_Data!E:E=$I$44)*(January_Data!B:B=$D$1&""))

(Although generally it's better to avoid references to whole columns)
 

Watch MrExcel Video

Forum statistics

Threads
1,133,268
Messages
5,657,742
Members
418,411
Latest member
Excellency

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