Sometimes, you may have a date value stored as text in Excel, such as “2024-02-10” or “10-Feb-2024”. In this case, you may want to extract the year, month, or day part of the date for further analysis or formatting. One way to do this is to use the LEFT, MID, and RIGHT functions in Excel.
The LEFT, MID, and RIGHT functions are text functions that allow you to extract a substring from a given text string, based on the number of characters you specify. The syntax of these functions are:
- LEFT(text, [num_chars]): returns the leftmost num_chars characters from text.
- MID(text, start_num, num_chars): returns num_chars characters from text, starting from the position specified by start_num.
- RIGHT(text, [num_chars]): returns the rightmost num_chars characters from text.
Procedure
To use the LEFT, MID, and RIGHT functions to extract date parts from a text string, you need to follow these steps:
- Identify the format of the date text string, and the position and length of each date part (year, month, and day) within the string.
- Use the LEFT function to extract the year part, if it is at the left end of the string. For example, if the date is “2024-02-10”, use =LEFT(“2024-02-10”, 4) to get “2024”.
- Use the RIGHT function to extract the year part, if it is at the right end of the string. For example, if the date is “10-Feb-2024”, use =RIGHT(“10-Feb-2024”, 4) to get “2024”.
- Use the MID function to extract the month or day part, if they are in the middle of the string. For example, if the date is “2024-02-10”, use =MID(“2024-02-10”, 6, 2) to get “02”, and =MID(“2024-02-10”, 9, 2) to get “10”.
- Optionally, you can convert the extracted date parts to numbers, by using the VALUE function. For example, if the month part is “Feb”, use =VALUE(“Feb”) to get 2.
- Optionally, you can combine the extracted date parts into a valid date value, by using the DATE function. For example, if the year, month, and day parts are 2024, 2, and 10, use =DATE(2024, 2, 10) to get 2/10/2024.
Example
Let’s see an example of how to use the LEFT, MID, and RIGHT functions to extract date parts from a text string in Excel. Suppose we have a list of dates in column A, in different formats, as shown below:
| A | B | C | D |
|---|---|---|---|
| Date | Year | Month | Day |
| 2024-02-10 | |||
| 10-Feb-2024 | |||
| 10/02/2024 | |||
| Feb 10, 2024 |
We want to extract the year, month, and day parts from each date, and store them in columns B, C, and D, respectively. Here are the formulas we can use:
- For the date in A2, which is in the format “yyyy-mm-dd”, we can use the following formulas:
- In B2, enter =LEFT(A2, 4) to get 2024.
- In C2, enter =MID(A2, 6, 2) to get 02.
- In D2, enter =MID(A2, 9, 2) to get 10.
- For the date in A3, which is in the format “dd-mmm-yyyy”, we can use the following formulas:
- In B3, enter =RIGHT(A3, 4) to get 2024.
- In C3, enter =VALUE(MID(A3, 4, 3)) to get 2.
- In D3, enter =LEFT(A3, 2) to get 10.
- For the date in A4, which is in the format “dd/mm/yyyy”, we can use the following formulas:
- In B4, enter =RIGHT(A4, 4) to get 2024.
- In C4, enter =MID(A4, 4, 2) to get 02.
- In D4, enter =LEFT(A4, 2) to get 10.
- For the date in A5, which is in the format “mmm dd, yyyy”, we can use the following formulas:
- In B5, enter =RIGHT(A5, 4) to get 2024.
- In C5, enter =VALUE(LEFT(A5, 3)) to get 2.
- In D5, enter =MID(A5, 5, 2) to get 10.
After entering the formulas, we get the following result:
| A | B | C | D |
|---|---|---|---|
| Date | Year | Month | Day |
| 2024-02-10 | 2024 | 02 | 10 |
| 10-Feb-2024 | 2024 | 2 | 10 |
| 10/02/2024 | 2024 | 02 | 10 |
| Feb 10, 2024 | 2024 | 2 | 10 |
Other Approaches
Besides using the LEFT, MID, and RIGHT functions, there are other ways to extract date parts from a text string in Excel. Here are some of them:
- Use the DATEVALUE function to convert the text string to a date value, and then use the YEAR, MONTH, and DAY functions to extract the date parts. For example, if the date is “2024-02-10”, use =DATEVALUE(“2024-02-10”) to get 2/10/2024, and then use =YEAR(2/10/2024) to get 2024, =MONTH(2/10/2024) to get 2, and =DAY(2/10/2024) to get 10. Note that this method requires the text string to be in a format that Excel can recognize as a date, such as “yyyy-mm-dd” or “dd-mmm-yyyy”.
- Use the TEXT function to convert the text string to a specific date format, and then use the LEFT, MID, and RIGHT functions to extract the date parts. For example, if the date is “10-Feb-2024”, use =TEXT(“10-Feb-2024”, “yyyy-mm-dd”) to get “2024-02-10”, and then use the same formulas as in the previous example. Note that this method requires you to know the format of the text string, and to specify the desired date format in the TEXT function.
