Is there a Microsoft Excel formula to extract a portion of text by specifying characters to exclude on the left and right?

Posted by:

Is there a Microsoft Excel formula to extract a portion of text by specifying characters to exclude on the left and right?

This seems like a tricky problem but it is actually simple. You just need to summon the power of Text Formulas! Microsoft Excel formula.

I’ve recreated your data and written a solution for you, but first, let’s look at Text formulas and decide which ones we need.

There are eight main types of Text Formulas:

=Trim – eliminates extra periods from commencement, middle, and end of the text string

=Proper – presents the first letter capital and the other letters lowercase of every word

=Left – returns a specified number of characters in a cell starting at left part of the string and going to the right the number of characters you define

=Right – returns a specified number of characters in a cell starting at right part of the string and going to the left the number of characters you define

=Mid – Returns a specified number of characters in a cell starting at a middle point you designate going to the right the number of characters you define

=Find – searches cell for a specified character(s) and returns the starting place in the string

=Len – returns the number of characters in the text string

=Value – returns the unformatted numerical data of a text string, this is helpful when Excel is recognizing numbers as text

For our solution, we will need to use a combination of =MID and =LEN.

Ok, let solve this one!

Here is our starting point. This is the data in your original question in row 1, and your desired output in row 2.

So, which formulas might come in handy here?

Formula 1: MID

The =MID formula will return a string of text from a cell, but you get to decide where you want to start, and you get to decide where to end.

How does this formula work?

=MID(text, start_num, num_chars)

  • text: this is your cell you want to slice up
  • start_num: this is the starting point, so if you want to start at the second character you would put 2
  • num_chars: this is how many characters you want AFTER the starting character

For example, if I had the text, “The Cats Are Awesome” in cell A1, and I just wanted “Cats”, then I would write:

=MID(A1,5,4)

This would just give us “Cats”, which starts 5 characters into the string, and is 4 characters long.

Let’s apply this formula to our text:

So, why do we have an 11 for start_num? Well, it is because the “C” is character 11 in our data for both B2 and C2.

  • – Amend: “C:\Users\RSH\AppData”

To get 11, just count the characters manually.

But what about 100?

Well, we want to cut the string off before the last quotation mark (“), but B2 is much shorter than C2.

So how do we find the right number? For each one, the total length will be different…..

We need a second formula!

Formula 2: LEN

The =LEN formula will count how many characters long a text string is. Very simple. The output is just a number.

=LEN(text)

  • text: this is your cell with the text

For example, if I had the text, “The Cats Are Awesome” in cell A1, and I wanted to know how long it was, I would write:

=LEN(A1)

Let’s apply this formula to our text, but with a twist:

Ok, so why did we subtract 11?

Well, what we really want is the length of the text string BETWEEN the quotation marks.

And we know the first quotation marks end at character 10.

SO, if we take the total length of the string, and subtract 10, we would get the length of the characters after the first quotation mark.

AND, if we take that number, and subtract 1, we can shave off the last quotation mark.

HENCE, 10+1 = 11.

So LEN(B2) – 11 will give us the length of the text BETWEEN quotation marks.

So the first text string has 20 characters between the quotation marks, and the second text string has 26 characters 🙂

Combining MID and LEN for the output!

This last part is easy. We just mash the formulas together.

Check it out.

For the final piece of the MID formula (num_chars), we just paste in the LEN formula.

Easy right?!

And look at the output. Works perfectly for two text strings of different length.

Now you should be a pro at MID & LEN formulas!

All of the text formulas are powerful, and each has very different use cases.

If you’re interested in learning all of Excel (including a whole module on cleaning, manipulating and analyzing data sets), I developed a business-focused course on mastering the 20% of Excel used for 80% of your work.

0

Add a Comment