Extract Numbers, Text, and Dates from Excel Cells
This guide explains the different ways to extract numbers, text, and dates from cells in Microsoft Excel and Google Sheets. You will learn the core functions, how to combine them for complex extractions, and how to avoid common errors.
Why You Need to Extract Data from Cells
Spreadsheets often contain combined data in a single cell, such as “Order #1234 – 2023-05-15” or “John Smith (45)”. To analyze, sort, or filter this data properly, you need to separate the numbers, text, or dates into their own columns. Manual copying is error-prone and slow, so Excel and Google Sheets provide functions to automate the process.
Basic Text Extraction: LEFT, RIGHT, and MID
The three fundamental functions for pulling out text are LEFT, RIGHT, and MID. They work the same in Excel and Google Sheets.
- LEFT(text, num_chars) – returns the first num_chars characters from the start of the text. Example:
=LEFT("ABC123", 3)returnsABC. - RIGHT(text, num_chars) – returns the last num_chars characters. Example:
=RIGHT("ABC123", 3)returns123. - MID(text, start_num, num_chars) – returns characters starting at position start_num. Example:
=MID("ABC123", 2, 2)returnsBC.
These are useful when the data is fixed-width, like a product code where the first 4 characters are letters and the rest are numbers.
Finding Position with FIND and SEARCH
When the data isn’t fixed-width, you need to locate a specific character or space. FIND and SEARCH return the position of a substring within a text string. The difference is that FIND is case-sensitive and does not allow wildcards, while SEARCH is not case-sensitive and allows wildcards like * and ?.
Example: =FIND(" ", "John Smith") returns 5 (the position of the space). You can then use that with LEFT to get the first name: =LEFT("John Smith", FIND(" ", "John Smith")-1) returns John.
Extracting Numbers from Mixed Text
If you have a cell like “Qty: 25” and you want just the number, you can use a combination of functions. One common approach is to use VALUE and SUBSTITUTE to remove non-numeric characters. For example, if the cell A1 contains “Qty: 25”, you can use:
=VALUE(SUBSTITUTE(A1, "Qty: ", ""))
This replaces the text “Qty: ” with an empty string, leaving “25”, and VALUE converts it to a number. For more complex extractions where numbers are mixed with letters (e.g., “AB12CD34”), you can use an array formula or a custom function. In Excel 365, you can use TEXTJOIN with an array of characters to keep only digits:
=VALUE(TEXTJOIN("", TRUE, IFERROR(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)*1, "")))
This is an array formula, so in older Excel versions you need to press Ctrl+Shift+Enter. In Google Sheets, you can use =REGEXEXTRACT(A1, "\d+") to extract the first number sequence.
Extracting Dates from Text
Dates stored as text are a common problem. For example, a cell might contain “2023-05-15” as text, or “May 15, 2023”. To convert text to a real date, use the DATEVALUE function. It works with dates that Excel recognizes, like “5/15/2023” or “15-May-2023”.
If the date is embedded in a longer string, you can first extract the date part using MID or REGEXEXTRACT (in Google Sheets), then apply DATEVALUE. For example, if A1 contains “Order placed on 2023-05-15”, you can use:
=DATEVALUE(MID(A1, FIND("2023", A1), 10))
This finds the position of “2023” and takes the next 10 characters (the date). Then DATEVALUE converts it to a serial number, which you can format as a date.
Common Mistakes and How to Avoid Them
- Mixing up text and number formats: A cell may look like a number but be stored as text. Use
VALUE()to convert, or check for a green triangle in Excel. - Off-by-one errors in position: When using
FINDandMID, remember thatMIDstarts at the position you specify. If you want the character after a space, add 1 to the position. - Locale differences: Date formats vary by region (e.g., DD/MM/YYYY vs MM/DD/YYYY).
DATEVALUEuses your system’s locale, so a date like “05/06/2023” could be interpreted as May 6 or June 5 depending on your settings. - Array formulas not entered correctly: In older Excel, array formulas need Ctrl+Shift+Enter. If you see a #VALUE! error, that might be the cause.
Using Text to Columns and Flash Fill
For simple, one-time extractions, you don’t always need formulas. Excel’s Text to Columns feature (found under the Data tab) can split a column based on a delimiter like a comma or space. It also has a fixed-width option.
Flash Fill (Excel 2013 and later) can automatically fill in a column based on a pattern you type. For example, if you type the first name from a full name in a neighboring cell, Flash Fill will suggest the rest. It’s not a formula, so it doesn’t update automatically, but it’s fast for cleaning up data.
Frequently Asked Questions
How do I extract only numbers from a cell with mixed text and numbers?
Use a combination of SUMPRODUCT and MID in Excel, or REGEXEXTRACT in Google Sheets. For Excel, the array formula =VALUE(TEXTJOIN("", TRUE, IFERROR(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)*1, ""))) extracts all digits. For Google Sheets, =REGEXEXTRACT(A1, "\d+") gets the first number sequence.
Why is my DATEVALUE function returning #VALUE?
This usually means the text is not a recognized date format. Check if the date has extra spaces, is in a format like “2023/05/15” (which Excel may not recognize), or if your system’s locale uses a different date order. Try using DATE with LEFT, MID, and RIGHT to parse the parts manually.
Can I extract text after a specific character, like a hyphen?
Yes. Use =RIGHT(A1, LEN(A1) - FIND("-", A1)). This finds the hyphen’s position and takes everything after it. If there are multiple hyphens, use SEARCH with the start position to find the second one.
Does Flash Fill work in Google Sheets?
No, Flash Fill is an Excel feature. In Google Sheets, you can use the SPLIT function to divide text by a delimiter, or use REGEXEXTRACT for pattern-based extraction.
How do I extract a date from a cell that also has a time?
If the cell contains a real date-time value (like 5/15/2023 14:30), you can use =INT(A1) to get the date part and =MOD(A1,1) for the time. Then format the result as a date. If it’s text, you need to parse the date portion manually.
What is the difference between FIND and SEARCH?
FIND is case-sensitive and does not allow wildcards. SEARCH is not case-sensitive and allows wildcards like * and ?. For example, SEARCH("a", "ABC") returns 1, but FIND("a", "ABC") returns #VALUE because it can’t find a lowercase ‘a’.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.