LEFT ()
This function displays a specified number of characters from
the left hand side of a piece of text or number.
Best view in desktop mode.
A
|
B
|
C
|
D
|
E
|
|
1
|
Text
|
Number Of
Characters Required |
Left String
|
||
2
|
Alan Jones
|
1
|
A
|
=LEFT(A2,B2)
|
|
3
|
Alan Jones
|
2
|
Al
|
=LEFT(A3,B3)
|
|
4
|
Alan Jones
|
3
|
Ala
|
=LEFT(A4,B4)
|
|
5
|
Cardiff
|
6
|
Cardif
|
=LEFT(A5,B5)
|
|
6
|
ABC123
|
4
|
ABC1
|
=LEFT(A6,B6)
|
|
7
|
|||||
8
|
|||||
9
|
|||||
10
|
Type "No of Characters Required" in function
|
||||
11
|
Name
|
Left String
|
|||
12
|
Alan Jones
|
Ala
|
=LEFT(A12,3)
|
||
13
|
Alan Jones
|
Ala
|
=LEFT(A13,3)
|
||
14
|
Alan Jones
|
Ala
|
=LEFT(A14,3)
|
||
15
|
Cardiff
|
Car
|
=LEFT(A15,3)
|
||
16
|
ABC123
|
ABC
|
=LEFT(A16,3)
|
||
Syntax
=LEFT(OriginalText,
NumberOfCharactersRequired)
Formatting
No special formatting is needed.
For example
In below example, we will use left function with find
function. The following table was used to extract the first name of a person
from their full name.
A
|
B
|
C
|
D
|
|
1
|
Full Name
|
First Name
|
||
2
|
Alan Jones
|
Alan
|
=LEFT(A2,FIND(" ",A2)-1)
|
|
3
|
Bob Smith
|
Bob
|
=LEFT(A3,FIND(" ",A3)-1)
|
|
4
|
Carol Williams
|
Carol
|
=LEFT(A4,FIND(" ",A4)-1)
|
|
FIND() function was used to locate position
of the space (in number) between the first and last name.
Like in Alan
Jones, find function
[FIND(" ",A2)] will give the value of space 5 as 5th
position.
And we will do minus 1 from 5 because it includes space
position. The length of the first name is equal to the position of the space
minus one character.
Like [FIND(" ",A2)-1], so it will become 4.
LEFT() function can now extract the first
name based on the position of the space.
Like [LEFT(A2,FIND(" ",A2)-1)] i.e. [LEFT(A2,4)].
No comments:
Post a Comment