Count characters in a cell

[stuartw]

Hi Guys

Just a quick one this (famous last words).

I'm trying to count the number of characters (including spaces) in a cell - is there a formula for this?? =CELL I thought would do it but doesn't

PS What I'm actually trying to do is return the Surname in a cell containing a full name. I can get the first name easy enough with =left(A1,Find(" ",A1)-1 (I take no credit for that...) but can't get the surname - I thought I might be able to use =right but can't.

Thanks
Stu

 

[VoG]

Try

=LEN(A1)

 

[schielrn]

Try:

=len(A1)

Hope that helps.

 

[Kaps_mr2]

Stuart,

LEN(A1) will return the length of the string in cell A1.

So you need:-

=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

but this assumes you have no middle names /double barrell surnames !!!Thks

Kaps

 

[VoG]

Try this

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:74px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Fred Smith</td><td >Fred</td><td >Smith</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=LEFT(A1,FIND<span style=' color:008000; '>(" ",A1)</span>-1)</td></tr><tr><td >C1</td><td >=RIGHT(A1,LEN<span style=' color:008000; '>(A1)</span>-FIND<span style=' color:008000; '>(" ",A1)</span>)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[stuartw]

Bah! Thanks for the response guys I knew there was a formula somewhere - think it's just that time of day...

Stu

 

[Gerald Higgins]

=LEN(A1)

will return the length of a text string.

To return text after the first instance of a space (such as SMITH from JOHN SMITH)
try something like this

=mid(A1,find(" ",a1&" ",1)+1,300)

 

[Gerald Higgins]

You could also use Data, Text to Columns, using space character as a delimiter.

 

[ChrisM]

use LEN(A1) to get the character count.

What you want is to find the space, use that as the starting point, then grab all the characters after that. To do that you need to use Mid()

=MID(cell,starting point, length)

=MID(A1,point right after the space, length of last name)

=MID(A1,SEARCH(" ",A1,1)+1,LEN(A1) - SEARCH(" ",A1,1)-1)

the first SEARCH bit gives you the character position of the space, we want to start one space over so we +1.

the LEN - SEARCH bit gives you the length of the last name. LEN returns total characters, maybe 15. 5 in first name, one in space, and 8 in last name. SEARCH, again, returns where the space is, which would be #6. So, we are saying length of last name = 15 - 6 - 1 = 8

 

[Gerald Higgins]

ChrisM - see my suggestion. I don't think you need to bother to check for the length of the second portion.