Hi all

Hopefully someone knows how to do this… I’ve got 156 rows of data in Excel and need to count the number of occurrences of a string within each cell in a specific column.

”““let DorQ3:=0; let Dor3:=”“”“; let DorT3:=1; let DorB3:=0; let DorQ2:=0; let Dor2:=”“”“; let DorT2:=1; let DorB2:=0; let DorQ:=0; let Dor:=”“”“; let DorT:=1; let DorB:=0; let CSub:=1; let Pan:=1; let V:=( “”|P|C”“ ); let Drw1:=1; let CarU:= Array(, , , , , , , , , ); let CarE:=No; let CarS:=Yes; let X1:=”“”“; let X2:=”“”“; let X3:=”“”“; let A:=MatXXType; let DorB:=0; let Pan1:=(if MatXPanelDoor then 2 else 1); let Car:=1; let Car1:=(if MatXCarcPanel then 2 else 1); let CarL:=(if MatXEndPanelL then 2 else 1); let CarR:=(if MatXEndPanelR then 2 else 1); let Ben:=0; let Kic:=0; let Fin:=1; let Fin1:=(if MatXFingerPanel then 2 else 1); ShowFriendlyPages(AALangFind(““Word”“,”“$Material”“), AALangFind(““QIQ”“, ““FP-MatCabinet.qiq”“), , ); setvalue(““MatXOptDor”“,1)”“”

I need to know how many occurrences there are of “Dor” in this, as well as the other 155 rows. Any ideas how this might be achieved?

Any help greatly appreciated, and worthy of free beers all night at the next pud I’m at :)

=COUNTIF(range,"string")

diddly-squat said:

=COUNTIF(range,"string")

With the data shown in my OP in yellow, the result from that formula is zero.

JTQ said:

diddly-squat said:

=COUNTIF(range,"string")

With the data shown in my OP in yellow, the result from that formula is zero.

then you have cocked up the formula

=COUNTIF(A1:A156,"Dor")

that should return the number of instances of the string “Dor” in the range A1:A156.

• With the data shown in my OP in yellow, the result from that formula is zero.

Did you replace “string” with the text you are looking for? Also:

Crt-H
Find What: Dor
Replace with: Dor
Replace All

will tell you how many there are…

furious said:

• With the data shown in my OP in yellow, the result from that formula is zero.

Did you replace “string” with the text you are looking for? Also:

Crt-H
Find What: Dor
Replace with: Dor
Replace All

will tell you how many there are…

That was my first thought but while it replaced properly it didn’t tell me how many were replaced.

Suppose your list of strings starts in A2, then:

In B2 enter: =FIND
In C2 enter: =FIND
Copy C2 across to cover more than the maximum number of “Dor“s. Each cell will contain an offset number to the position of the next Dor, until it can’t find any more, then it will display #VALUE.
In the next column (say R) enter =COUNTIF
Adjust the Q to whatever your last column is.
Copy Columns B to R (or whatever) down for the complete list.

I think you’d need some VBA to do it in a single cell.

• That was my first thought but while it replaced properly it didn’t tell me how many were replaced.

For the text in the first post my excel says:

Excel has completed its search and has made 14 replacements.

diddly-squat said:

JTQ said:

diddly-squat said:

=COUNTIF(range,"string")

With the data shown in my OP in yellow, the result from that formula is zero.

then you have cocked up the formula

=COUNTIF(A1:A156,"Dor")

that should return the number of instances of the string “Dor” in the range A1:A156.

That formula will count the number of cells that contain “Dor” and nothing else.

furious said:

• That was my first thought but while it replaced properly it didn’t tell me how many were replaced.

For the text in the first post my excel says:

Excel has completed its search and has made 14 replacements.

I have a very ancient version of Excel. Don’t think it knows how to count.

The Rev Dodgson said:

diddly-squat said:

JTQ said:

With the data shown in my OP in yellow, the result from that formula is zero.

then you have cocked up the formula

=COUNTIF(A1:A156,"Dor")

that should return the number of instances of the string “Dor” in the range A1:A156.

That formula will count the number of cells that contain “Dor” and nothing else.

ummm… yes… is that not what he wants??

The Rev Dodgson said:

Suppose your list of strings starts in A2, then:

In B2 enter: =FIND
In C2 enter: =FIND
Copy C2 across to cover more than the maximum number of “Dor“s. Each cell will contain an offset number to the position of the next Dor, until it can’t find any more, then it will display #VALUE.
In the next column (say R) enter =COUNTIF
Adjust the Q to whatever your last column is.
Copy Columns B to R (or whatever) down for the complete list.

I think you’d need some VBA to do it in a single cell.

Formulas are:
=FIND string in quotes,$A2
=FIND string in quotes,$A2, B2+1
=COUNTIF B2:Q2,>0 in quotes

With brackets where appropriate!

diddly-squat said:

The Rev Dodgson said:

diddly-squat said:

then you have cocked up the formula

=COUNTIF(A1:A156,"Dor")

that should return the number of instances of the string “Dor” in the range A1:A156.

That formula will count the number of cells that contain “Dor” and nothing else.

ummm… yes… is that not what he wants??

No, he wants the number of “Dors“s in a long string in each cell.

The Rev Dodgson said:

Formulas are:
=FIND string in quotes,$A2
=FIND string in quotes,$A2, B2+1
=COUNTIF B2:Q2,>0 in quotes

With brackets where appropriate!

Or click on quote, and see the originals!

diddly-squat said:

The Rev Dodgson said:

diddly-squat said:

then you have cocked up the formula

=COUNTIF(A1:A156,"Dor")

that should return the number of instances of the string “Dor” in the range A1:A156.

That formula will count the number of cells that contain “Dor” and nothing else.

ummm… yes… is that not what he wants??

Provided that it only counts Dor & not Dorsal or adorable etc.

The Rev Dodgson said:

diddly-squat said:

The Rev Dodgson said:

That formula will count the number of cells that contain “Dor” and nothing else.

ummm… yes… is that not what he wants??

No, he wants the number of “Dors“s in a long string in each cell.

I see…

ds – just seen how you got the formulas to display properly.

I’ll try and remember the at trick.

Formulas with ats:

Suppose your list of strings starts in A2, then:

In B2 enter: =FIND("Dor",$A2)
In C2 enter: =FIND("Dor",$A2,B2+1)
Copy C2 across to cover more than the maximum number of “Dor“s. Each cell will contain an offset number to the position of the next Dor, until it can’t find any more, then it will display #VALUE.
In the next column (say R) enter =COUNTIF(B2:Q2,">0")
Adjust the Q to whatever your last column is.
Copy Columns B to R (or whatever) down for the complete list.

I think you’d need some VBA to do it in a single cell.

All up, there are 6095 “Dor” references in the 156 rows. This includes DorQ3, DorT3, DorB3, DorQ2, DorQ, Dor, DorT, etc…

Just need to know how many instances of “Dor” there are (including those above, DorQ3, etc) in each individual cell.

Another Google search and apparently this one works. Getting a different result tho.

=SUM(LEN(B1)-LEN(SUBSTITUTE(B1,"Dor","")))/LEN("Dor")

Yellow data from OP returns 38 instances of “Dor”.

JTQ said:

All up, there are 6095 “Dor” references in the 156 rows. This includes DorQ3, DorT3, DorB3, DorQ2, DorQ, Dor, DorT, etc…

Just need to know how many instances of “Dor” there are (including those above, DorQ3, etc) in each individual cell.

My formulas will give you that.

JTQ said:

Another Google search and apparently this one works. Getting a different result tho.

=SUM(LEN(B1)-LEN(SUBSTITUTE(B1,"Dor","")))/LEN("Dor")

Yellow data from OP returns 38 instances of “Dor”.

That’s pretty clever.

I get 14 using that with your OP data, the same as I get with my method.

The Rev Dodgson said:

JTQ said:

Another Google search and apparently this one works. Getting a different result tho.

=SUM(LEN(B1)-LEN(SUBSTITUTE(B1,"Dor","")))/LEN("Dor")

Yellow data from OP returns 38 instances of “Dor”.

That’s pretty clever.

I get 14 using that with your OP data, the same as I get with my method.

Not sure why you’re getting 14 when I’m constantly getting 38??

JTQ said:

The Rev Dodgson said:

JTQ said:

Another Google search and apparently this one works. Getting a different result tho.

=SUM(LEN(B1)-LEN(SUBSTITUTE(B1,"Dor","")))/LEN("Dor")

Yellow data from OP returns 38 instances of “Dor”.

That’s pretty clever.

I get 14 using that with your OP data, the same as I get with my method.

Not sure why you’re getting 14 when I’m constantly getting 38??

I don’t know either.

Are you sure the string in the OP is the same as the one you are getting 38 with?

Did you try my method?

The Rev Dodgson said:

JTQ said:

The Rev Dodgson said:

That’s pretty clever.

I get 14 using that with your OP data, the same as I get with my method.

Not sure why you’re getting 14 when I’m constantly getting 38??

I don’t know either.

Are you sure the string in the OP is the same as the one you are getting 38 with?

Did you try my method?

Yeh I did and still got 38.

Just checked it again now and the string in OP is only half the length of what’s in the cell. Turns out going to the beginning of the cell and pressing Ctrl-Shift-End doesn’t highlight the entire cell’s contents like I thought it would.

”““let DorQ3:=0; let Dor3:=”“”“; let DorT3:=1; let DorB3:=0; let DorQ2:=0; let Dor2:=”“”“; let DorT2:=1; let DorB2:=0; let DorQ:=0; let Dor:=”“”“; let DorT:=1; let DorB:=0; let CSub:=1; let Pan:=1; let V:=( “”|P|C”“ ); let Drw1:=1; let CarU:= Array(, , , , , , , , , ); let CarE:=No; let CarS:=Yes; let X1:=”“”“; let X2:=”“”“; let X3:=”“”“; let A:=MatXXType; let DorB:=0; let Pan1:=(if MatXPanelDoor then 2 else 1); let Car:=1; let Car1:=(if MatXCarcPanel then 2 else 1); let CarL:=(if MatXEndPanelL then 2 else 1); let CarR:=(if MatXEndPanelR then 2 else 1); let Ben:=0; let Kic:=0; let Fin:=1; let Fin1:=(if MatXFingerPanel then 2 else 1); ShowFriendlyPages(AALangFind(““Word”“,”“$Material”“), AALangFind(““QIQ”“, ““FP-MatCabinet.qiq”“), , ); setvalue(““MatXOptDor”“,1)”“”

38.

Ahh …

Seems this forum truncates long strings?

"""let DorQ3:=0; let Dor3:=""""; let DorT3:=1; let DorB3:=0; let DorQ2:=0; let Dor2:=""""; let DorT2:=1; let DorB2:=0; let DorQ:=0; let Dor:=""""; let DorT:=1; let DorB:=0; let CSub:=1; let Pan:=1; let V:=( ""|P|C"" ); let Drw1:=1; let CarU:= Array(, , , , , , , , , ); let CarE:=No; let CarS:=Yes; let X1:=""""; let X2:=""""; let X3:=""""; let A:=MatXXType; let DorB:=0; let Pan1:=(if MatXPanelDoor then 2 else 1); let Car:=1; let Car1:=(if MatXCarcPanel then 2 else 1); let CarL:=(if MatXEndPanelL then 2 else 1); let CarR:=(if MatXEndPanelR then 2 else 1); let Ben:=0; let Kic:=0; let Fin:=1; let Fin1:=(if MatXFingerPanel then 2 else 1); ShowFriendlyPages(AALangFind(""Word"",""$Material""), AALangFind(""QIQ"", ""FP-MatCabinet.qiq""), , ); setvalue(""MatXOptDor"",1)"""

Yes, yes it does.

Nevermind anyway .. .turns out there’s 38

Cheers :)

For reference

JTQ said:

Yes, yes it does.

Nevermind anyway .. .turns out there’s 38

Cheers :)

Glad its working then.

JTQ said:

Another Google search and apparently this one works. Getting a different result tho.

=SUM(LEN(B1)-LEN(SUBSTITUTE(B1,"Dor","")))/LEN("Dor")

Yellow data from OP returns 38 instances of “Dor”.

that’s rather elegant

square bracket fail

cryptically worded phrase

Gratuitously irrelevant observation