Counting words in excel formula

[Here I go again]

I was wondering if there was a formula for counting text in excel I know the basic formula however what I want it to count is as follows. 1-tandem 2-tandem 3-tandem but be able to pick up the numbers in front of the word as well and condense it all into one count. Is this possible? I can set it up to count the word tandem by itself but I am doing a scheduling spreadsheet and have more than one tandem on certain jobs so being able to count up all the trucks under certain names would be beneficial.

[vlady]

So in your example 1-tandem 2-tandem 3-tandem what is the answer or total count you expect.

[Here I go again]

I would like to get the total count of tandems in a field. Cause through the whole work book will be the example above and also the same numbers exponential with other names such as super tandems and super dumps. The idea is that I will be able to keep count of my trucks as I schedule so I don't over book myself. I will try to upload later for some reason I cannot upload my sample sheet.

[vlady]

I think it is better to attach a sample workbook.


To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Here I go again]

For some reason I cannot upload an example format it will not allow me to. I get the spinning beach ball of death when I try to upload. I will try periodically throughout the day.

[FDibbins]

This will do the count for you. You can hard-code the word, or reference it...
=(LEN(A1)-LEN(SUBSTITUTE(A1,"tandem","")))/LEN("tandem")

[Here I go again]

=COUNTIFS(A3:H154,"1-tandem") this is the formula I use to count my trucks however this formula does not pick up the trucks labeled 2-tandem, 3-tandem, 4-tandem etc. What I would like to find is a formula that could add up all of the tandems with the numbers and dashes included if that is possible. So my outcome count would tell me that (using the example from earlier) I could get a total count of 4 tandems. The formula suggested by FDibbins did not work it gave me back a 0 instead of the 4 that were on my work sheet. Perhaps I entered it wrong?

[AlKey]

This should do it

=COUNTIF(A3:H154,"*tandem*")

[Here I go again]

A lot closer however I do have other trucks labeled 1-super tandem as a different truck that I do not wish to pick up. I there anyway to make it for just the specific tandem? Or will it always pick up all cells with the word tandem in it?

[Tony Valko]

Post some sample data and tell us what result(s) you expect.

[AlKey]

Yes,

=COUNTIF(A3:H154,"*tandem*")-COUNTIF(A3:H154,"*1-super tandem*")

[Tony Valko]

If we're just taking WAG's here's my offering:

=COUNTIF(A2:A10,"?-tandem")

[Here I go again]

Thank you very much AlKey that was the formula I needed all I had to do was remove the 1- in front of super tandems in order to get it to work thank you very much.

[AlKey]

You're welcome and thank you for your feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).