top of page
Search
  • Writer's pictureImran Din

Mhmm that's Mid. - Reading between the lines using the Mid function.

Before Mid was a cool term used to describe not so cool things, it was a function in Excel used to extract characters from the middle of text strings.


Let's say we had a list of email addresses in the format of FirstName.LastName@Gmail.com. Our challenge is to extract the Last Names.



Now all we really need to do is grab the text between the Period and @ symbol.


First let's find out where the Periods and @ symbols are:


We'll use the Search formula as follows:

=SEARCH(whatToSearchFor,whereToSearch)

So, in our case, we're looking for the period in the A column:

=SEARCH(".",A2)

And same for the @ symbol:

=SEARCH("@",A2)


Next, we'll use the Mid function to find the Last Name.


The Mid function works as follows:

=Mid(originalText, whereToStart, howManyCharactersToInclude)

We want to search in the A column, so for the originalText we'll use A2:

=Mid(A2, whereToStart, howManyCharactersToInclude)

The Last Names start from after the period, so we'll use the period position + 1:

=Mid(A2, B2+1, howManyCharactersToInclude)

Now, we want to include from where the period starts to the @ symbol. So we'll minus the periodPosition from the @Position and that will give us the length of the last name + the @.


We don't want the @ itself so finally, we'll minus an extra 1. Our final formula will look something like this:


=Mid(A2, B2+1, C2-B2-1)

And there you have it!



Now you have a list of all the Last Names! Great Success!

5 views0 comments

댓글


bottom of page