Excel Lessons

Extract text in Excel with LEFT, RIGHT and MID

Take the first, last or middle characters out of a code, an ID or a date written as text. LEFT, RIGHT, MID and LEN with examples you can check.

Published . 6 min read.

By Ali, Founder, Excel Lessons

To extract text in Excel, use LEFT, RIGHT or MID. LEFT takes the start, RIGHT takes the end and MID takes the middle. LEFT and RIGHT need the text and a number of characters. MID needs the text, a start position and a number of characters.

Here is a file many people get every week. A courier sends an export of shipment IDs. Each ID holds the country, the date the parcel was sent and a parcel number. You need them in separate columns.

Row A
1 Shipment ID
2 FR20250314-0087
3 DE20250402-0112
4 ES20250519-0045
5 IT20250611-2308
6 NL20250702-112

In A2, FR is the country. 20250314 is the date, 14 March 2025. 0087 is the parcel number. Look at A6 before you go on. It is one character shorter than the others.

Count the positions once

Excel counts characters from 1, not from 0. Write the positions under one ID before you type a formula.

123456789101112131415
FR20250314-0087

The date starts at 3 and is 8 characters long. The hyphen is character 11. The parcel number starts at 12. Every formula below uses these numbers.

LEFT: the country

=LEFT(A2,2) returns FR. The 2 means 2 characters from the start.

The second argument is a count, not a position. This is the mistake everybody makes once. =LEFT(A2,3) returns FR2, not the third character. For one character from the middle, use MID. =MID(A2,3,1) returns 2.

RIGHT: the parcel number

=RIGHT(A2,4) returns 0087. The zeros stay, because the result is text.

For a parcel number, that is usually right. It is a label, and the zeros are part of it. Keep it as text if you will look it up in another list of codes.

If you need a real number, put VALUE around it. =VALUE(RIGHT(A2,4)) returns 87. You can check which one you have. =ISNUMBER(RIGHT(A2,4)) returns FALSE.

MID: the date in the middle

=MID(A2,3,8) returns 20250314. It starts at character 3 and takes 8.

That looks like a date, but it is 8 digits of text. You cannot add days to it, and a month filter will not see it. So take the 3 parts out one by one:

  • Year: =MID(A2,3,4) returns 2025.
  • Month: =MID(A2,7,2) returns 03.
  • Day: =MID(A2,9,2) returns 14.

Then give the 3 parts to DATE, which builds a real date.

=DATE(MID(A2,3,4),MID(A2,7,2),MID(A2,9,2)) returns 45730.

Format the cell as a date and it shows 14 March 2025, a Friday. DATE accepts the text 03 as the number 3, so you do not need VALUE here.

Why 45730? A date in Excel is a number of days. The article on why dates are numbers explains it.

The same idea works for a date that arrived as the text 14.03.2025 in C2. =DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)) returns 45730 too. RIGHT takes the year, MID takes the month and LEFT takes the day.

The row that is one character short

A6 holds NL20250702-112. Its parcel number has 3 digits, not 4. Somebody at the courier typed it by hand.

=RIGHT(A6,4) returns -112. There is no error and nothing turns red. The hyphen goes into your column, and you only find it when a lookup fails.

LEN finds these rows. It counts the characters in a cell. =LEN(A5) returns 15, and =LEN(A6) returns 14.

In a spare column, type =LEN(A2)<>15 and fill it down. A6 returns TRUE. Every other row returns FALSE.

Filter that column for TRUE. Read those rows before you trust any column you built.

Take the rest, not a fixed count

The fix for A6 is to start at 12 and take everything after it. Use a large number for the length.

=MID(A6,12,99) returns 112. =MID(A2,12,99) still returns 0087.

MID never goes past the end of the text. It stops at the last character. The 99 simply means "the rest".

LEFT and RIGHT work the same way. =LEFT(A2,50) returns the whole ID, with no error.

MID has one more thing to know. Start past the end and it returns empty text. =MID(A2,20,4) returns nothing, and =LEN(MID(A2,20,4)) returns 0. A column that turns empty usually means the positions moved. The data is still there.

When the positions move, find them

Some codes have no fixed shape. Then you cannot count the positions once. Let FIND count them for each row.

=FIND("-",A6) returns 11, the position of the hyphen.

=MID(A6,FIND("-",A6)+1,99) returns 112. It starts one character after the hyphen, wherever the hyphen is.

Names work the same way, with a space in place of the hyphen. The article on splitting a full name covers that case.

Watch for spaces you cannot see. An ID pasted with a space at the end has a LEN of 16. RIGHT then returns 087 and the space. TRIM removes it.

The finished sheet

B2 is =LEFT(A2,2). C2 is the DATE formula, formatted as a date. D2 is =MID(A2,12,99). All 3 are filled down to row 6.

Row A B C D
1 Shipment ID Country Sent Parcel
2 FR20250314-0087 FR 14 Mar 2025 0087
3 DE20250402-0112 DE 2 Apr 2025 0112
4 ES20250519-0045 ES 19 May 2025 0045
5 IT20250611-2308 IT 11 Jun 2025 2308
6 NL20250702-112 NL 2 Jul 2025 112

The dates sit on the right of the cell, because they are numbers. The parcel numbers sit on the left, because they are text.

Questions people ask

How do I remove the first 2 characters?

Take everything from character 3. =MID(A2,3,99) returns 20250314-0087. =RIGHT(A2,LEN(A2)-2) gives the same answer.

How do I remove the last 5 characters?

Take LEN minus 5 from the left. =LEFT(A2,LEN(A2)-5) returns FR20250314.

What if I leave out the number in LEFT?

LEFT and RIGHT then take 1 character. =LEFT(A2) returns F. MID has no default, so it needs all 3 arguments.

Can I do it without a formula?

Yes. Data, then Text to Columns, then Fixed width splits a column at the positions you click. It changes the cells, and it does not update when the data changes. It can also drop the zeros from 0087. Set that column to Text in the last step of the wizard to keep them.

The lessons below use codes like these. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.

Related articles