# Count number after text

1. ## Count number after text

So I'm relatively green when it comes to excel. I wan't to set up a way to distinguish specific sets of numbers by containing text in the same cell, and create a formula that'll count the numbers based on the accompanying text. for example: "Blanket:2" in one cell, and "Square: 2" in another, while having a formula to count the numbers individually. I do know that I could set up two columns or rows and have them count based on the cells, but will not work on the project that I am making.

2. ## Re: Count number after text

Hi John, welcome to the forum. A small sample workbook (NOT a picture) would really help us to solve your problem quickly and accurately.
Please remove any sensitive or extraneous info.
Try to preserve the original layout so our solutions fit your workbook (optional).
Provide “realistic” data. Include any variations the code or formula must address.
IMPORTANT: Simulate some results to demonstrate what you want.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Scroll down to Manage Attachments and click.

3. ## Re: Count number after text

just a thought, if you have a column with Blanket:2 and Square: 2 you could always use text to columns with a colon as the delimiter and separate the values into another column then simply add them.

4. ## Re: Count number after text

Oh, sorry! Here's the workbook:

5. ## Re: Count number after text

Originally Posted by Sambo kid
just a thought, if you have a column with Blanket:2 and Square: 2 you could always use text to columns with a colon as the delimiter and separate the values into another column then simply add them.
Thank you for your reply, though I'm not sure if delimiters can help me in my case!

6. ## Re: Count number after text

is that how the data will be consistently shown? PTO: and a number or U.PTO: and a number? Nothing else?

7. ## Re: Count number after text

Try this ARRAY FORMULA* in B41,copied across:
``Please Login or Register  to view this content.``
...and similarly, in B44, copied across:
``Please Login or Register  to view this content.``
*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

8. ## Re: Count number after text

Yes, it will be consistently inputed like that

9. ## Re: Count number after text

This helps, a ton! thank you so much +rep!

10. ## Re: Count number after text

You're welcome, thanks for the rep.

BTW, If you ever have issues with these array formulas, make sure they're array-entered. Double-click on the cell to enter Edit Mode and press CTRL+SHIFT+ENTER (all at once). You should see curly brackets {} embrace the formula text, signifying array status.

11. ## Re: Count number after text

Here is a shorter version:
In B41
Formula:
`Please Login or Register  to view this content.`

For B44
Formula:
`Please Login or Register  to view this content.`

Array formulas
***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1