In “Save time by using Excel’s Left, Right, and Mid string functions,” I showed you how to extract substrings from a text entry in a spreadsheet cell. To review, the Left function returns the first (leftmost) n characters from a string and the Right function returns the rightmost n characters.

The Mid function requires two arguments—a start point and the n number of characters to return. In the first lesson, the start point for the string we wanted to extract from the middle of the string was constant. In other words, we knew we wanted to “grab” two characters out of the middle of our sample string, and we knew those two characters always started in the fourth position of the string.

Knowing that the middle string always starts in a certain position makes it easy to use the Mid function. But what do you do when the substring you want to extract could start anywhere within the string? As long as there’s something in the string that identifies the start of the substring, you can use the Find function with the Mid function to accomplish the mission.

Use Find to generate the start point for the Mid function call
Here’s a simple example to demonstrate how this works. Suppose you have a series of variable-length strings like those shown in Figure A. The only thing that’s consistent in those strings is the presence of the dash (or hyphen). Your challenge: You want to compose a formula that returns the first two characters following the dash.


Here’s the trick: You use the Find function to return the position of the dash in the string, and you use the Find function itself as the Mid function’s first argument.

The Find function takes the form:
=Find(thing_to_find,source)

As Figure B shows, the function =Find(“-“,A2) returns the value 6, since the dash appears in the sixth position of the string in A2. We copied this Find function down the column so you can see that it returns a different value each time, depending on where the dash appears in the strings in column A.


In order to extract the first two characters that follow the dash, you’ll use the Find function to locate the dash and add 1 to that result. (The Find function returns the location of the dash; you want to start extracting characters beginning at the next character, the one following the dash.)

So our formula looks like this: =Mid(A2,Find(“-“,A2)+1,2). That formula says, in English, “Look at A2; determine the position of the dash in A2; add 1 to that value; then return the next two characters.” Figure C shows our results.


Only the beginning
We’ve just scratched the surface of what you can do by combining the Find and Mid functions. In a future article, we’ll show you how to use a variation of this technique to transpose first and last names to format them as Last, First.
To comment on this article or to share your favorite Excel tip, please post a comment below or drop us a note.