Learning Excel: MID and SEARCH
Cutting from the middle
Natalie wants to know which guests have an address at the same company. The domain comes after the at sign, but that sits in a different place for everyone. First you search, then you cut.
MID
MID takes a piece out of the middle of a text. You give the text, the start position and the number of characters.
=MID(D2,2,2) starts at the second character of T01-COD and takes two: 01, the table number.
=MID(D2,2,2)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | First name | Last name | Table | Mobile | As typed | Result | |
| 2 | Sadie | De Vere | sadie.devere@mail.co.uk | T01-COD | 07700 900 467 | sadie de vere | 01 |
| 3 | James | Pearson | james.pearson@yahoo.co.uk | T01-STEAK | 07700 900 233 | JAMES PEARSON | |
| 4 | Amira | El Idrissi | amira@elidrissi.co.uk | T02-VEG | 07700 900 654 | amira el idrissi | |
| 5 | Liam | Walters | liam.w@gmail.com | T02-COD | 07700 900 566 | Liam Walters | |
| 6 | Faye | Clark | faye.clark@hotmail.co.uk | T03-STEAK | 07700 900 456 | faye clark | |
| 7 | Bill | Moss | bill@moss-bakery.co.uk | T03-VEG | 07700 900 321 | BILL moss | |
| 8 | Libby | Jennings | libby.jennings@yahoo.co.uk | T04-COD | 07700 900 899 | libby jennings | |
| 9 | Owen | Van Dyke | owen.vandyke@mail.co.uk | T04-STEAK | 07700 900 030 | owen VAN DYKE | |
| 10 |
SEARCH
SEARCH gives the position of a piece of text. =SEARCH("@",C2) gives 13: the at sign is the thirteenth character of Sadie's address.
Combine it with MID: =MID(C2,SEARCH("@",C2)+1,100) starts one character after the @ and takes the rest. Ask for more characters than there are and you simply get everything up to the end.
=MID(C2,SEARCH("@",C2)+1,100)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | First name | Last name | Table | Mobile | As typed | Result | |
| 2 | Sadie | De Vere | sadie.devere@mail.co.uk | T01-COD | 07700 900 467 | sadie de vere | mail.co.uk |
| 3 | James | Pearson | james.pearson@yahoo.co.uk | T01-STEAK | 07700 900 233 | JAMES PEARSON | |
| 4 | Amira | El Idrissi | amira@elidrissi.co.uk | T02-VEG | 07700 900 654 | amira el idrissi | |
| 5 | Liam | Walters | liam.w@gmail.com | T02-COD | 07700 900 566 | Liam Walters | |
| 6 | Faye | Clark | faye.clark@hotmail.co.uk | T03-STEAK | 07700 900 456 | faye clark | |
| 7 | Bill | Moss | bill@moss-bakery.co.uk | T03-VEG | 07700 900 321 | BILL moss | |
| 8 | Libby | Jennings | libby.jennings@yahoo.co.uk | T04-COD | 07700 900 899 | libby jennings | |
| 9 | Owen | Van Dyke | owen.vandyke@mail.co.uk | T04-STEAK | 07700 900 030 | owen VAN DYKE | |
| 10 |
Try it yourself
In G2, put everything before the @ in Sadie's address.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | First name | Last name | Table | Mobile | As typed | Result | ||
| 2 | Sadie | De Vere | sadie.devere@mail.co.uk | T01-COD | 07700 900 467 | sadie de vere | ||
| 3 | James | Pearson | james.pearson@yahoo.co.uk | T01-STEAK | 07700 900 233 | JAMES PEARSON | ||
| 4 | Amira | El Idrissi | amira@elidrissi.co.uk | T02-VEG | 07700 900 654 | amira el idrissi | ||
| 5 | Liam | Walters | liam.w@gmail.com | T02-COD | 07700 900 566 | Liam Walters | ||
| 6 | Faye | Clark | faye.clark@hotmail.co.uk | T03-STEAK | 07700 900 456 | faye clark | ||
| 7 | Bill | Moss | bill@moss-bakery.co.uk | T03-VEG | 07700 900 321 | BILL moss | ||
| 8 | Libby | Jennings | libby.jennings@yahoo.co.uk | T04-COD | 07700 900 899 | libby jennings | ||
| 9 | Owen | Van Dyke | owen.vandyke@mail.co.uk | T04-STEAK | 07700 900 030 | owen VAN DYKE | ||
| 10 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson