Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting characters in a cell
I have the following formula in cell A1 =10+10+10
i would like cell B1 to have a formula that counts the number of + signs in cell A1. In this example it would return 2 I would like cell C1 to have a formula that counts the numbers in cell A1. In this case it would be 3 Thanks in advance for your help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting characters in a cell
Caveat -
This should be used in XL02 or later. Earlier versions may crash when copying this type of formula containing cell to other WBs. Start my creating a 'named' formula that will return the actual formula from A1; Click in B1, then, <Insert <Name <Define, In the "Names In Workbook" box, enter a short name, say frmla for formula. In the "Refers To" box, replace whatever's there with this formula: =GET.CELL(6,A1) Then <OK What you have now is a 'relative' *named formula* that when entered in *any* cell, will return the contents of the cell (text, data, formulas) from the *previous* column. So, if you enter =frmla in B1, you'll see the formula displayed from A1 (=10+10+10) So, *now*, enter this in B1: =LEN(frmla)-LEN(SUBSTITUTE(frmla,"+","")) And this in C1: =B1+1 -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "ram" wrote in message ... I have the following formula in cell A1 =10+10+10 i would like cell B1 to have a formula that counts the number of + signs in cell A1. In this example it would return 2 I would like cell C1 to have a formula that counts the numbers in cell A1. In this case it would be 3 Thanks in advance for your help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting characters in a cell
Thanks for your help RD it works great
"RagDyeR" wrote: Caveat - This should be used in XL02 or later. Earlier versions may crash when copying this type of formula containing cell to other WBs. Start my creating a 'named' formula that will return the actual formula from A1; Click in B1, then, <Insert <Name <Define, In the "Names In Workbook" box, enter a short name, say frmla for formula. In the "Refers To" box, replace whatever's there with this formula: =GET.CELL(6,A1) Then <OK What you have now is a 'relative' *named formula* that when entered in *any* cell, will return the contents of the cell (text, data, formulas) from the *previous* column. So, if you enter =frmla in B1, you'll see the formula displayed from A1 (=10+10+10) So, *now*, enter this in B1: =LEN(frmla)-LEN(SUBSTITUTE(frmla,"+","")) And this in C1: =B1+1 -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "ram" wrote in message ... I have the following formula in cell A1 =10+10+10 i would like cell B1 to have a formula that counts the number of + signs in cell A1. In this example it would return 2 I would like cell C1 to have a formula that counts the numbers in cell A1. In this case it would be 3 Thanks in advance for your help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting characters in a cell
Appreciate the feed-back.
-- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "ram" wrote in message ... Thanks for your help RD it works great "RagDyeR" wrote: Caveat - This should be used in XL02 or later. Earlier versions may crash when copying this type of formula containing cell to other WBs. Start my creating a 'named' formula that will return the actual formula from A1; Click in B1, then, <Insert <Name <Define, In the "Names In Workbook" box, enter a short name, say frmla for formula. In the "Refers To" box, replace whatever's there with this formula: =GET.CELL(6,A1) Then <OK What you have now is a 'relative' *named formula* that when entered in *any* cell, will return the contents of the cell (text, data, formulas) from the *previous* column. So, if you enter =frmla in B1, you'll see the formula displayed from A1 (=10+10+10) So, *now*, enter this in B1: =LEN(frmla)-LEN(SUBSTITUTE(frmla,"+","")) And this in C1: =B1+1 -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "ram" wrote in message ... I have the following formula in cell A1 =10+10+10 i would like cell B1 to have a formula that counts the number of + signs in cell A1. In this example it would return 2 I would like cell C1 to have a formula that counts the numbers in cell A1. In this case it would be 3 Thanks in advance for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting characters inside a cell | Excel Discussion (Misc queries) | |||
Automatically printing psted text over 250 characters to another cell | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Display text 1024 characters in a cell | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |