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

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

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

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

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