Use Mid, Find & Len function to extract substring

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
amiqullah@gmail.com	gmail.com	MID(A2,FIND("@",A2,1)+1,LEN(A2)-FIND("@",A2,1))
anjum@shalimar.com	shalimar.com	MID(A3,FIND("@",A3,1)+1,LEN(A3)-FIND("@",A3,1))
nawazanjum@malikgulfaraz.com	malikgulfaraz.com	MID(A4,FIND("@",A4,1)+1,LEN(A4)-FIND("@",A4,1))
Mianamjad@quresh.com	quresh.com	MID(A5,FIND("@",A5,1)+1,LEN(A5)-FIND("@",A5,1))