Tips on how to Extract a Substring in Excel Utilizing FIND() and MID() Capabilities


Learn to use MID() and FIND() after which mix them to parse substrings from inconsistent knowledge.

Parsing knowledge is a typical job in Excel. For essentially the most half, you’ll accomplish that when you’ll want to work with substrings relatively than the entire supply values. For example, you may need to parse a retailer or buyer identification quantity from a transaction string that features all of the details about a selected transaction.

Due to Excel’s string capabilities, parsing is straightforward, so long as the supply values are constant. String capabilities, akin to MID() have to know the place the substring you’re extracting begins and ends. When that data is unknown or inconsistent from one worth to a different, you’ll need to work a bit more durable.

On this article, I’ll present you easy methods to mix two string capabilities, MID() and FIND() to resolve the issue of parsing structurally inconsistent knowledge. I’m utilizing Microsoft 365, however these capabilities can be found in standalone variations.

Bounce to:

What are string capabilities?

MID() and FIND() are string capabilities. In Excel, string capabilities allow you to retrieve particular characters, additionally referred to as a substring, from a supply string. There are a number of string capabilities:

  • LEFT(): Will get characters from the left facet of the supply string.
  • RIGHT(): Will get characters from the suitable facet of the supply string.
  • MID(): Will get characters from the center of the supply string.
  • LEN(): Returns the variety of characters within the supply string.
  • FIND(): Returns the place of a selected character inside the supply string.

By utilizing these capabilities individually or by combining them, you may rapidly return a substring from a supply string.

Tips on how to use the MID() operate

When extracting a substring — a smaller piece of the supply string — from the center of the supply string, you may think about using MID(), which makes use of two arguments to retrieve characters from the center of a string. It makes use of the shape:

MID()(textual content, start_num, num_chars)

the place textual content is the supply string, start_num is the place of the primary character you need to parse and num_chars is the full variety of characters you need to parse.

Let’s take a look at a fast instance utilizing the pattern knowledge proven in Determine A.

Determine A

Excel Mid function example.
The MID() operate returns a substring from the center of a supply string.

Particularly, let’s return the primary character following the hyphen character from every supply string within the dataset as follows:

  1. In B2, enter the next operate
    =MID(A2,7,1)
  2. Copy it to the remaining cells within the dataset.

The argument, 7, begins the parse on the seventh character in A2 and 1 specifies that the operate parses just one character, returning the letter B from the supply string in A2.

Should you copy the operate to the remaining cells within the dataset, you’ll discover that it typically fails. Our unique job was to return the primary character following the hyphen character. Sadly, the hyphen isn’t all the time within the seventh place — that place is inconsistent.

If the hyphen have been positioned constantly, MID() would work. However what do you do when the substring you need to extract may begin wherever inside the string? Right here’s the trick: You employ the FIND() operate to return the place of the hyphen after which use these outcomes because the MID() operate’s second argument. So subsequent, let’s learn to use FIND().

SEE: 3 Ways to Suppress 0 in Excel.

Tips on how to use the FIND() operate

Excel’s FIND() operate parses a substring by discovering the place of a selected character or string. This operate makes use of the shape

FIND(find_text, textual content, [start_num])

the place find_text is the substring you’re searching for, textual content is the supply string you’re looking out and start_num specifies the character at which to start the search. When omitted, the search all the time begins with the primary character in textual content.

Now, let’s use FIND() to return the place of the hyphen character:

  1. In C2, enter the operate
    =FIND(“-“,A2)
  2. Copy it to the remaining cells within the dataset (Determine B).

Determine B

Excel Find function example.
The FIND() operate returns the place of the discovered character.

As you may see, FIND() returns a worth, not a personality. This worth represents the place of the discovered character, on this case, the hyphen character.

SEE: How to parse data in Microsoft Excel.

Tips on how to mix MID() and FIND()

We all know a few issues at this level: MID() returns a substring from the center of a supply string and FIND() returns the place of a selected string inside a supply string. When the supply knowledge is inconsistent in construction however every worth has a typical character, you may mix the 2 capabilities to get the job finished.

We’ve already used FIND() to return the place of the hyphen in every worth. Now, let’s mix it with MID() to return the character that instantly follows the hyphen in every worth, as follows:

  1. In D2, enter the next operate
    =MID(A2,FIND(“-“,A2)+1,1)
  2. Copy it to the remaining cells within the dataset (Determine C).

Determine C

Excel combine function example.
Mix MID() and FIND().

Now, let’s evaluate how the mix works utilizing the primary worth within the dataset, K2445-B2100:

=MID(A2,FIND("-",A2)+1,1)

=MID(K2445-B2100,FIND("-",K2445-B2100)+1,1)

=MID(K2445-B2100,(6+1),1)

=MID(K2445-B2100,(7),1)

=MID("B",1)

=B

The FIND() operate returns 6, the place of the primary hyphen in K2445-B2100. The MID() operate then makes use of the worth 6 (plus 1) to return B. We add the worth 1 to maneuver the extraction to the suitable by one character. We’re parsing the character to the suitable of the hyphen, not the hyphen.

Due to FIND(), the hyphen character may be wherever inside the supply string and we will nonetheless discover a character relative to it.



Source link