How to
extract first name, middle name and last name from name
Example 1
1. Suppose we have names like this below
and we want to extract first name and last name out of this list. First of all
we need to find out the pattern of this list.
The pattern is that the first name appears after first space or period
and the 2nd name is between 2nd and 3rd space
and in some cases between 3rd and 4th space till to the
end where the first bracket list.
Names
|
1. Marilyn
Monroe (1926 – 1962) American actress, singer, model
|
2. Abraham
Lincoln (1809 – 1865) US President during American civil war
|
3. Mother
Teresa (1910 – 1997) Macedonian Catholic missionary nun
|
4. John F.
Kennedy (1917 – 1963) US President 1961 – 1963
|
5. Martin Luther
King (1929 – 1968) American civil rights campaigner
|
6. Nelson
Mandela (1918 – 2013) South African President anti-apartheid
campaigner
|
7. Queen
Elizabeth II (1926 – ) British monarch since 1954
|
8. Winston
Churchill (1874 – 1965) British Prime Minister during WWII
|
9. Donald
Trump (1946 – ) Businessman, US President.
|
10. Bill
Gates (1955 – ) American businessman, founder of Microsoft
|
11. Muhammad
Ali (1942 – 2016) American Boxer and civil rights campaigner
|
12. Mahatma
Gandhi (1869 – 1948) Leader of Indian independence movement
|
13. Margaret Thatcher (1925
– 2013) British Prime Minister 1979 – 1990
|
14. Christopher
Columbus (1451 – 1506) Italian explorer
|
15. Charles
Darwin (1809 – 1882) British scientist, theory of evolution
|
16. Elvis
Presley (1935 – 1977) American musician
|
17. Albert Einstein (1879
– 1955) German scientist, theory of relativity
|
18. Paul
McCartney (1942 – ) British musician, member of Beatles
|
Extracting First Name
v
To
find the first period or space, we will use MID() FUNCTION along with search()
or Find() function
v
=mid(text,start_num,num_char)
i)
text
= A2 (as below)
ii)
start_num
= char position after 1st space
=SEARCH("
",A2,1) + 1 (result is 3 in the above case)
iii)
num_char
= length of the char to extract. In this case, it will be after start_num till
2nd space position.
a) First find the 2nd space
char position
=SEARCH("
",A2,SEARCH("
",A2,1)+1) (result = 11)
In place 1 (as in ii) in
the same function, paste the same search function by adding 1.
b) Deduct 1st space char
position from 2nd space char position and reduce it by 1
v
Now
paste the above in the mid() function
=MID(A2,SEARCH(" ",A2,1)+1,SEARCH("
",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)-1)
Extracting Last Name when
there is no middle name
=mid(text,start_num,num_char)
v
First
find the start_num in mid() function
start_num would be the
position after the 2nd space position
=SEARCH(" ",A2,SEARCH(" ",A2,1)+1)
+1
v
num_char
(length of char)
a)
First
find the 1st bracket position [“(“]
=SEARCH("(",A2,1)
b)
Deduct
2 from above to get the position of last char of last name
c)
Get
the no. of chars for
num_char = (Bracket positon -2) – (2nd
space position+1)
num_char =
(SEARCH("(",A2,1)-2 - SEARCH(" ",A2,SEARCH("
",A2,1)+1))
v
Now
create the final formula to the get the last name
=MID(A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1,SEARCH("(",A2,1)-2-SEARCH("
",A2,SEARCH(" ",A2,1)+1))
Extracting Last Name when there is middle name
=mid(text,start_num,num_char)
v
First
find the start_num in mid() function
start_num would be the
position after the 3rd space position
=SEARCH("
",A6,SEARCH(" ",A6,SEARCH(" ",A6,1)+1)+1)
In order create this
function, use the following technique:
·
=SEARCH("
",A6,1)
·
=SEARCH("
",A6, SEARCH("
",A6,1)+1) [put the same function in place of 1 (start_num) and add 1]
·
=SEARCH("
",A6,SEARCH(" ",A6,SEARCH(" ",A6,1)+1)+1) [put the same function in place of 1 (start_num) and add 1]
v
num_char
(length of char)
a)
First
find the position of 1st bracket position [“(“]
=SEARCH("(",A6,1)
b)
Deduct
2 from above to get the position of last char of last name
=SEARCH("(",A6,1)-2
c)
Find
the position of 1st char of last name just after the 3rd
space position
=SEARCH(" ",A6,SEARCH("
",A6,SEARCH(" ",A6,1)+1)+1)
d)
Get
the no. of chars for
num_char = (b - c)
num_char = (SEARCH("(",A6,1)-2
- =SEARCH(" ",A6,SEARCH(" ",A6,SEARCH(" ",A6,1)+1)+1)
v
Now
create the final formula to the get the last name
= =MID(A6,SEARCH(" ",A6,SEARCH("
",A6,SEARCH("
",A6,1)+1)+1)+1,SEARCH("(",A6,1)-2-SEARCH("
",A6,SEARCH(" ",A6,SEARCH(" ",A6,1)+1)+1))
No comments:
Post a Comment