Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count text within numbers
Hi,
Is it possible to count how many times a letters appears in a text string. Example 1 234 N8 0,4xF9 I would like a formula that gives me the result of 1 as N only appears once. Is this possible? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count text within numbers
Try this:
=LEN(A1)-LEN(SUBSTITUTE(A1,"N","")) Note that this is case sensitive. The above will not count lower case n. -- Biff Microsoft Excel MVP "LiAD" wrote in message ... Hi, Is it possible to count how many times a letters appears in a text string. Example 1 234 N8 0,4xF9 I would like a formula that gives me the result of 1 as N only appears once. Is this possible? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count text within numbers
If you want to count both upper and lower case letters as being the same:
1 2n4 N8 0,4xFN9n =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"N","")) Count of N + n = 4 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =LEN(A1)-LEN(SUBSTITUTE(A1,"N","")) Note that this is case sensitive. The above will not count lower case n. -- Biff Microsoft Excel MVP "LiAD" wrote in message ... Hi, Is it possible to count how many times a letters appears in a text string. Example 1 234 N8 0,4xF9 I would like a formula that gives me the result of 1 as N only appears once. Is this possible? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count text within numbers
In what way do you intend SUM(LEN(A2)) to be different from LEN(A2) ?
In what way do you intend SUM(LEN(SUBSTITUTE(A2,"N",""))) to be different from LEN(SUBSTITUTE(A2,"N","")) ? Is there some use of the SUM function of which the rest of us are unaware? Also, why the array formula? Where is the array? What would be different if we just used enter instead of CSE? -- David Biddulph "francis" wrote in message ... one other way with array formula, to be confirm by Ctrl,Shift and Enter =SUM(LEN(A2))-SUM(LEN(SUBSTITUTE(A2,"N","")))/LEN("N") This is case sensitive, it will return 0 if you put a "n" instead of "N" in your data. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "LiAD" wrote: Hi, Is it possible to count how many times a letters appears in a text string. Example 1 234 N8 0,4xF9 I would like a formula that gives me the result of 1 as N only appears once. Is this possible? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count text within numbers
Hi,
You can also try this array formula (Ctrl+Shift+Enter) =SUM(1*(MID($D$13,ROW(INDIRECT("1:"&LEN(D13))),1)= "N")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "LiAD" wrote in message ... Hi, Is it possible to count how many times a letters appears in a text string. Example 1 234 N8 0,4xF9 I would like a formula that gives me the result of 1 as N only appears once. Is this possible? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
assigning numbers to text and count the text | Excel Discussion (Misc queries) | |||
assigning numbers to text and count them | Excel Discussion (Misc queries) | |||
Count numbers within text | Excel Worksheet Functions | |||
count 2 criteria text or numbers | Excel Worksheet Functions | |||
Count, numbers and text | Excel Discussion (Misc queries) |