Counting characters within a cell
In order to help update an existing spreadsheet, I want to be able to count
either the number of numbers in a given cells, or alternatively the number off addition signs. i.e. A1 =125+65+122+35 In A2 I'd like to be able to count either the number of numbers(eg. 4 numbers in this example where 125 is a single number), or the number of +'s (and then add one) so that I can calculate the delivery fees based on the number of deliveries (which is what the cell represents) Changing the spreadsheet to hold all the data in seperate cells while being easier from this point of view is not an option. Thanks |
Counting characters within a cell
If A1 contains 125+65+122+35 as text (not a formula =125+65+122+35 showing
cell value as 347) then following in B1 will give the count of + symbol plus 1; =LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))+1 "Alco Engineer" wrote: In order to help update an existing spreadsheet, I want to be able to count either the number of numbers in a given cells, or alternatively the number off addition signs. i.e. A1 =125+65+122+35 In A2 I'd like to be able to count either the number of numbers(eg. 4 numbers in this example where 125 is a single number), or the number of +'s (and then add one) so that I can calculate the delivery fees based on the number of deliveries (which is what the cell represents) Changing the spreadsheet to hold all the data in seperate cells while being easier from this point of view is not an option. Thanks |
Counting characters within a cell
Thanks Sheeloo, but it isn't a text cell.
Instead the cell is an addition formula (i.e =125+55+36) and displays 216 as the result. I need to be able to determine how many numbers have been used to calculate that result. Is there any way I can do this? "Sheeloo" wrote: If A1 contains 125+65+122+35 as text (not a formula =125+65+122+35 showing cell value as 347) then following in B1 will give the count of + symbol plus 1; =LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))+1 "Alco Engineer" wrote: In order to help update an existing spreadsheet, I want to be able to count either the number of numbers in a given cells, or alternatively the number off addition signs. i.e. A1 =125+65+122+35 In A2 I'd like to be able to count either the number of numbers(eg. 4 numbers in this example where 125 is a single number), or the number of +'s (and then add one) so that I can calculate the delivery fees based on the number of deliveries (which is what the cell represents) Changing the spreadsheet to hold all the data in seperate cells while being easier from this point of view is not an option. Thanks |
Counting characters within a cell
You can not do this using a formula. You will have to use a macro...
I can write that for you if you want... Need more info though... Can the formula be in any cell of the sheet or only in specific columns/rows? Do you want the count in the same sheet (where?) or in a separate sheet? etc. etc... The macro below will put the formula in A1-A100 in B1-B100 of sheet 2 "Alco Engineer" wrote: Thanks Sheeloo, but it isn't a text cell. Instead the cell is an addition formula (i.e =125+55+36) and displays 216 as the result. I need to be able to determine how many numbers have been used to calculate that result. Is there any way I can do this? "Sheeloo" wrote: If A1 contains 125+65+122+35 as text (not a formula =125+65+122+35 showing cell value as 347) then following in B1 will give the count of + symbol plus 1; =LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))+1 "Alco Engineer" wrote: In order to help update an existing spreadsheet, I want to be able to count either the number of numbers in a given cells, or alternatively the number off addition signs. i.e. A1 =125+65+122+35 In A2 I'd like to be able to count either the number of numbers(eg. 4 numbers in this example where 125 is a single number), or the number of +'s (and then add one) so that I can calculate the delivery fees based on the number of deliveries (which is what the cell represents) Changing the spreadsheet to hold all the data in seperate cells while being easier from this point of view is not an option. Thanks |
Counting characters within a cell
Hey mate,
Thanks anyway but it was just an attempt to help save some time. I think that operating the Macro would take more effort than counting and entering the numbers by hand. I appreciate you help anyway. "Sheeloo" wrote: You can not do this using a formula. You will have to use a macro... I can write that for you if you want... Need more info though... Can the formula be in any cell of the sheet or only in specific columns/rows? Do you want the count in the same sheet (where?) or in a separate sheet? etc. etc... The macro below will put the formula in A1-A100 in B1-B100 of sheet 2 "Alco Engineer" wrote: Thanks Sheeloo, but it isn't a text cell. Instead the cell is an addition formula (i.e =125+55+36) and displays 216 as the result. I need to be able to determine how many numbers have been used to calculate that result. Is there any way I can do this? "Sheeloo" wrote: If A1 contains 125+65+122+35 as text (not a formula =125+65+122+35 showing cell value as 347) then following in B1 will give the count of + symbol plus 1; =LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))+1 "Alco Engineer" wrote: In order to help update an existing spreadsheet, I want to be able to count either the number of numbers in a given cells, or alternatively the number off addition signs. i.e. A1 =125+65+122+35 In A2 I'd like to be able to count either the number of numbers(eg. 4 numbers in this example where 125 is a single number), or the number of +'s (and then add one) so that I can calculate the delivery fees based on the number of deliveries (which is what the cell represents) Changing the spreadsheet to hold all the data in seperate cells while being easier from this point of view is not an option. Thanks |
Counting characters within a cell
You could also write a UDF instead of a macro. Morefunc already has
one written, you could download and install it. Actually, it would take 2 of their's, named FORMULATEXT and WORDCOUNT. Or maybe just FORMULATEXT if you counted all the "+". |
All times are GMT +1. The time now is 11:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com