Find number of spaces in a string (instr)

[DejaVu]

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

[JE McGimpsey]

One way:

=LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))

In article <[email protected]>,
DejaVu <[email protected]> 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

[DejaVu]

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

[Dave Peterson]

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

[Fredg]

I was able to do it in a very crude way. I used the MID function to get each number in the cell. The +0 converts the extracted number back to number format as the MID function makes it text.

Maybe someone will see this mess and write a nice VBA function that reads each number into an Array and then adds them together. I tried 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 have 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 the 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)


Fred