Sunday, May 18, 2014

LEN Function in MS-Excel

C
D
3
Text
Length
4
Alan Jones
10
 =LEN(C4)
5
Bob Smith
9
 =LEN(C5)
6
Carol Williams
14
 =LEN(C6)
7
Cardiff
7
 =LEN(C7)
8
ABC123
6
 =LEN(C8)

What Does It Do?
This function counts the number of characters, including spaces and numbers, in a piece of text.

Syntax
=LEN(Text)

Formatting
No Special formatting is needed.

Example
This example shows how the =LEN() function is used in a formula which extracts the second name from a text entry containing both first and second names.

C
D
23
Original Text
24
Carol Williams
6
 =FIND(" ",C24)
This is the position of the space.
27
Carol Williams
8
 =LEN(C24)-FIND(" ",C24)
                                                               This is the length of the second name.
                                                               Calculated by taking the overall length of the
                                                               complete name and subtracting the position of the 
                                                               space.
                                                     
                                                               =RIGHT(C24,LEN(C24)-FIND(" ",C24))
                                                               This is just the second name.
                                                               Calculated by using the =RIGHT() function to
                                                               extract the rightmost characters up to the length 
                                                               of the second name.
                                                               


No comments:

Post a Comment