Excel Practice (Pt.1): FIND, MID, and LEN

Hi, I need to use Excel today at work, and to save me time, I asked ChatGPT. Gosh, she is so smart. I don't understand it yet, so I will analyze the code and practice the FIND, MID, and LEN functions as well since I don't have a hang of them.

#1: Definition

MID: pull a substring from the middle of the original text string. Technically speaking, the MID function returns the number of characters starting at the position you specify.

The Excel MID function has the following arguments:

MID (text, start_num, num_chars)

Where:

• Text is the original text string.

• Start_num is the position of the first character that you want to extract.

• Num_chars is the number of characters to extract.

---

LEN: get the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.

The Excel LEN function has the following arguments:

=LEN(text)

Where:

Text (required argument) – This is the text for which we wish to calculate the length. We can provide the text argument for the function:

• Directly

• As a string returned from another formula

• As a reference to a cell containing a string

---

FIND: locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. (so confusing lol)

FIND(find_text, within_text, [start_num])

Where:

• Find_text    Required. The text you want to find.

• Within_text    Required. The text containing the text you want to find.

• Start_num    Optional. Specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

Miriam McGovern
Formula	Description	Result
=FIND("M",A2)	Position of the first "M" in cell A2	1
=FIND("m",A2)	Position of the first "M" in cell A2	6
=FIND("M",A2,3)	Position of the first "M" in cell A2, starting with the third character	8

---

#2: Solve my problem

From ChatGPT

If you want to extract a specific portion of text in Excel from "This application was submitted by Study 4 Better Future ( Lazar ) on 30/01/2024 17:29 | Download application" to "Study 4 Better Future ( Lazar )", you can use the combination of Excel functions such as FIND, MID, and LEN.

Assuming your text is in cell A1, you can use the following formula in another cell to extract the desired text:

```excel

= MID(A1, FIND("submitted by ", A1) + LEN("submitted by "), FIND(" on ", A1) - FIND("submitted by ", A1) - LEN("submitted by "))

```

This formula assumes that "submitted by" is a constant part of your text and extracts the text between "submitted by" and " on". It should give you the result "Study 4 Better Future ( Lazar )".

Remember to adjust the cell references and modify the formula based on the structure of your actual data.

---

Explanation:

MID (text, start_num, num_chars)

text: A1

start_num: the whole point of this is to find the end/ position of the string "submitted by ", which is the space.

FIND("submitted by ", A1): the result gonna be a number of the position of ''submitted by'', at the starting point: 22

LEN("submitted by "): the length of this string (13), then added up with FIND, will be the ending point: 35

num_chars: 31. Find how many characters are there between '' on'' (start from space) and the end/ position of the string "submitted by "

FIND(" on ", A1) - FIND("submitted by ", A1) - LEN("submitted by ") = 66 - (22+13)