# Count characters in a cell

#### stuartw

##### Board Regular
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Try

=LEN(A1)

Try:

=len(A1)

Hope that helps.

#### Kaps_mr2

##### Well-known Member
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

##### Legend
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

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

Stu

#### Gerald Higgins

##### Well-known Member
Code:
``=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
Code:
``=mid(A1,find(" ",a1&" ",1)+1,300)``

#### Gerald Higgins

##### Well-known Member
You could also use Data, Text to Columns, using space character as a delimiter.

#### ChrisM

##### Well-known Member
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

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

Replies
3
Views
136
Replies
15
Views
304
Replies
5
Views
198
Replies
3
Views
286
Replies
5
Views
201

1,190,703
Messages
5,982,395
Members
439,777
Latest member
daleEH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back