Use Mid, Find & Len function to extract substring
Mid function requires three arguments, first the text (could be a cell reference) to look in, the start point and number of characters to return.
What to do when the start position and number of characters to return
(length) is unknown?
For example we have a list of email addresses and we want to
extract the domain which is after @.
Here comes the use of Find function along Mid function. The Find function, have three arguments.
First one is text to find, second is within text (could be a cell reference)
and third the starting point of search is optional.
Suppose in cell A2 we have an email address
amiqullah@gmail.com . With Find function, we can know the position of @. So the
syntax would =FIND("@",A2,1) and the result is 10. So the domain
name in this mail address is starting from 11 onward.
Now let us use the Mid function to extract the domain name.
Example is given below.
v
The email address is in cell A2. So first specify
the text we are looking in =Mid(A2,
v
After that we need to tell the starting point
for that use Find function and add 1 so if the Position of @ is 10, we want our
search start from 11. FIND("@",A2,1)+1
v
Now the third argument is to specify the length
of text that we want to extract. For that first pull the length of our email
address len(a2) and then exclude the length of characters that are till @. For
that use the same Find function. So complete syntax is LEN(A2)FIND("@",A2,1).
v
And our complete formula is MID(A2,FIND("@",A2,1)+1,LEN(A2)FIND("@",A2,1))
In case of any confusion, please feel free to contact me.
A

B

Formula in Cell B

gmail.com

MID(A2,FIND("@",A2,1)+1,LEN(A2)FIND("@",A2,1))


shalimar.com

MID(A3,FIND("@",A3,1)+1,LEN(A3)FIND("@",A3,1))


malikgulfaraz.com

MID(A4,FIND("@",A4,1)+1,LEN(A4)FIND("@",A4,1))


quresh.com

MID(A5,FIND("@",A5,1)+1,LEN(A5)FIND("@",A5,1))

No comments:
Post a Comment