Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 17th 05, 09:20 AM
Larry Wallis
 
Posts: n/a
Default Calculating number of letters in a string ...

Looking for a way to calculate the number of characters in a text string. I
can use the LEN function.

e.g. If A1 contains "News Group", the number of characters is 9. Using the
LEN(A1) formula would give me 10 (includes the space).

How can I use this formula to omit the space?

Thanks.

Larry.



  #2   Report Post  
Old February 17th 05, 09:35 AM
Ragdyer
 
Posts: n/a
Default

Try this:

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Larry Wallis" wrote in message
...
Looking for a way to calculate the number of characters in a text string.

I
can use the LEN function.

e.g. If A1 contains "News Group", the number of characters is 9. Using the
LEN(A1) formula would give me 10 (includes the space).

How can I use this formula to omit the space?

Thanks.

Larry.



  #3   Report Post  
Old February 17th 05, 09:35 AM
Geovas
 
Posts: n/a
Default

suppose the Word "News Group" is in cell A1 use the following formula in B1:
=LEN(SUBSTITUTE(A1," ",""))

This will first substitute all spaces in the string with nothing and then
return the length of the new string giving you the desired length....

Hope this Helps



Ο "Larry Wallis" έγραψε στο μήνυμα
...
Looking for a way to calculate the number of characters in a text string.

I
can use the LEN function.

e.g. If A1 contains "News Group", the number of characters is 9. Using the
LEN(A1) formula would give me 10 (includes the space).

How can I use this formula to omit the space?

Thanks.

Larry.




  #4   Report Post  
Old February 17th 05, 10:11 AM
Larry Wallis
 
Posts: n/a
Default

Thanx RD.

Larry.


"Ragdyer" wrote in message
...
Try this:

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

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------

-

"Larry Wallis" wrote in message
...
Looking for a way to calculate the number of characters in a text

string.
I
can use the LEN function.

e.g. If A1 contains "News Group", the number of characters is 9. Using

the
LEN(A1) formula would give me 10 (includes the space).

How can I use this formula to omit the space?

Thanks.

Larry.





  #5   Report Post  
Old February 17th 05, 10:11 AM
Larry Wallis
 
Posts: n/a
Default

And thanx to u too Geovas.

Larry.


"Geovas" wrote in message
...
suppose the Word "News Group" is in cell A1 use the following formula in

B1:
=LEN(SUBSTITUTE(A1," ",""))

This will first substitute all spaces in the string with nothing and then
return the length of the new string giving you the desired length....

Hope this Helps



Ο "Larry Wallis" έγραψε στο μήνυμα
...
Looking for a way to calculate the number of characters in a text

string.
I
can use the LEN function.

e.g. If A1 contains "News Group", the number of characters is 9. Using

the
LEN(A1) formula would give me 10 (includes the space).

How can I use this formula to omit the space?

Thanks.

Larry.








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating interest on number of days in the period Ron Excel Worksheet Functions 0 January 18th 05 12:59 AM
How can I assign a number to a string? Jog Dial Excel Discussion (Misc queries) 3 January 14th 05 03:44 AM
Read Text File into Excel Using VBA Willie T Excel Discussion (Misc queries) 13 January 8th 05 12:37 AM
count number of occurences within a string Gabriel Excel Worksheet Functions 2 November 25th 04 04:17 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM


All times are GMT +1. The time now is 10:06 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017