Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find number of spaces in a string (instr)
Is there any way to find out how many spaces (or any other character) are in a string? I need to find out how many '+' are in a string. TIA DejaVu -- DejaVu ------------------------------------------------------------------------ DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629 View this thread: http://www.excelforum.com/showthread...hreadid=466881 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find number of spaces in a string (instr)
One way:
=LEN(A1)-LEN(SUBSTITUTE(A1,"+","")) In article , DejaVu wrote: Is there any way to find out how many spaces (or any other character) are in a string? I need to find out how many '+' are in a string. TIA DejaVu |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find number of spaces in a string (instr)
Thanks for the help JE... but it didnt seem to work. It keeps telling me -_Sub_or_Function_not_defined_-, then it highlites *Substitute*. Maybe this will help. Here is my specific need. I am getting a couple of strings from our AS/400 database. The strings are numbers. For example, say I am getting the number of Apples, and the number of Oranges. _*Apples____Oranges*_ 1___________4 5___________7 4___________1 4___________6 9___________5 I may get all the apples in one cell ... so I want cell A1 to look like this when its done. =1+5+4+4+9 In a perfect world, I would want apples and oranges to be in the same cell... like this: =(1+5+4+4+9)/(4+7+1+6+5) The reason I need to know the number of plus signs, was for the first part of this. I was averaging out the apples, so I needed to know how many times apples showed up. I hope this is a better explanation!! Thanks JE, DejaVu -- DejaVu ------------------------------------------------------------------------ DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629 View this thread: http://www.excelforum.com/showthread...hreadid=466881 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find number of spaces in a string (instr)
J.E. gave you a worksheet function--you'd use it in a cell.
Are you looking for a VBA suggestion? dim myStr as string mystr = worksheets("Sheet1").range("a1").value msgbox len(mystr) - len(application.substitute(mystr,"+","")) if you're using xl2k or higher, you could use: msgbox len(mystr) - len(replace(mystr,"+","")) DejaVu wrote: Thanks for the help JE... but it didnt seem to work. It keeps telling me -_Sub_or_Function_not_defined_-, then it highlites *Substitute*. Maybe this will help. Here is my specific need. I am getting a couple of strings from our AS/400 database. The strings are numbers. For example, say I am getting the number of Apples, and the number of Oranges. _*Apples____Oranges*_ 1___________4 5___________7 4___________1 4___________6 9___________5 I may get all the apples in one cell ... so I want cell A1 to look like this when its done. =1+5+4+4+9 In a perfect world, I would want apples and oranges to be in the same cell... like this: =(1+5+4+4+9)/(4+7+1+6+5) The reason I need to know the number of plus signs, was for the first part of this. I was averaging out the apples, so I needed to know how many times apples showed up. I hope this is a better explanation!! Thanks JE, DejaVu -- DejaVu ------------------------------------------------------------------------ DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629 View this thread: http://www.excelforum.com/showthread...hreadid=466881 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find number of spaces in a string (instr)
I was able to do it in a *very* crude way. I used the MID function t get each number in the cell. The +0 converts the extracted number bac to number format as the MID function makes it text. Maybe someone will see this mess and write a nice VBA function tha reads each number into an Array and then adds them together. I trie but I lack the skills. Here is the non programmer way: For instance: Cell A1 = 15449 Cell B1 = 47165 Cell C1: = (MID(A1,1,1))+0 + (MID(A1,2,1))+0 + (MID(A1,3,1))+0 + (MID(A1,4,1) +0 +(MID(A1,5,1))+0 Cell D1: =(MID(B1,1,1))+0 + (MID(B1,2,1))+0 + (MID(B1,3,1))+0 + (MID(B1,4,1) +0 +(MID(B1,5,1))+0 Cell E1: =AVERAGE(C1,D1) Highlight C1, D1 and E1 and then drag down (assuming that you hav other values in columns A and B). Of course if you numbers vary in length you will have more work to do. --- You could combine all this into one cell to acomplish what you asked for: "=(1+5+4+4+9)/(4+7+1+6+5)" which is not the same as averaging th two. Cell C1: =((MID(A1,1,1))+0 + (MID(A1,2,1))+0 + (MID(A1,3,1))+0 + (MID(A1,4,1) +0 +(MID(A1,5,1))+0) / ((MID(B1,1,1))+0 + (MID(B1,2,1))+0 (MID(B1,3,1))+0 + (MID(B1,4,1)) +0 +(MID(B1,5,1))+0) Fre -- Fred ----------------------------------------------------------------------- Fredg's Profile: http://www.excelforum.com/member.php...fo&userid=2632 View this thread: http://www.excelforum.com/showthread.php?threadid=46688 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is there an equal fxn for 'InStr' in excel. Not Find or Search | Excel Worksheet Functions | |||
How do I find only number caracter in a string? | Excel Worksheet Functions | |||
find last number in a string? | Excel Discussion (Misc queries) | |||
Find String in another string - only between spaces | Excel Worksheet Functions | |||
number as string and adding spaces to it | Excel Programming |