is there a way to write a macro that looks at a field of
data and counts the number of spaces in it. i.e.:

Field 1
|Dave Jones 2100 Wind Street Anytown AK 11021|

So I would want the macro to recognize there being "7"
spaces in this record and based on that place it into a
specific worksheet. Any help is greatly appreciated.
thank you.

Peter.

Hi
do you need a VBA solution or would a worksheet formula work as well?.
For the latter one try
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

--
Regards
Frank Kabel
Frankfurt, Germany

"Peter" schrieb im Newsbeitrag
...
is there a way to write a macro that looks at a field of
data and counts the number of spaces in it. i.e.:

Field 1
|Dave Jones 2100 Wind Street Anytown AK 11021|

So I would want the macro to recognize there being "7"
spaces in this record and based on that place it into a
specific worksheet. Any help is greatly appreciated.
thank you.

Peter.

len(sStr) - len(replace(sStr," ",""))

use application.Substitute if using xl97 or earlier.

--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
is there a way to write a macro that looks at a field of
data and counts the number of spaces in it. i.e.:

Field 1
|Dave Jones 2100 Wind Street Anytown AK 11021|

So I would want the macro to recognize there being "7"
spaces in this record and based on that place it into a
specific worksheet. Any help is greatly appreciated.
thank you.

Peter.

thanks to both of you for the help. i appreciate your
sharing of knowledge
-----Original Message-----
is there a way to write a macro that looks at a field of
data and counts the number of spaces in it. i.e.:

Field 1
|Dave Jones 2100 Wind Street Anytown AK 11021|

So I would want the macro to recognize there being "7"
spaces in this record and based on that place it into a
specific worksheet. Any help is greatly appreciated.
thank you.

Peter.
.