Readers like you help support MUO. When you make a purchase using links on our site, we may earn an affiliate commission. Read More.
Microsoft Excel is great at working with both numbers and text---but if you're using both in the same cell, you might run into some difficulty. Fortunately, you can extract numbers or text from cells towork with your data more efficiently. We demonstrate several options, depending on the format that your data is currently in.
MAKEUSEOF VIDEO OF THE DAY
SCROLL TO CONTINUE WITH CONTENT
Excel Numbers Formatted as Text
This is a common situation, and---fortunately---very easy to deal with. Sometimes, cells that contain only numbers are incorrectly labeled or formatted as text, preventing Microsoft Excel from using them in operations.
You can see in the image below that the cells in column A are formatted as text, as indicated by the number format box. You might also see a green flag in the top left corner of each cell.
Convert Text to Number in Excel
If you see the green flag in the top left corner, select one or more cells, click the warning sign, and select Convert to Number.
Otherwise, select the cells and, in the Number Format menu in the Ribbon, select the default Number option.
If you need more granular options, right-click the highlighted cell/s and select Format Cells, which will open the respective menu. Here, you can customize the number format and add or remove decimals, add a 1,000 separator, or manage negative numbers.
Obviously, you can also use the Ribbon or Format Cells options outlined above to convert a number to text, or text to currency, time, or any other format you desire.
Apply Number Formatting With Excel's Paste Special
For this method to work, you'll need to enter a number (any number) into a cell; it's important that this cell is also formatted as a number. Copy that cell. Now, select all the cells that you want to convert to the number format, go to Home > Paste > Paste Special, select Formats to paste only the formatting of the cell you copied initially, then click OK.
This operation applies the format of the cell you copied to all selected cells, even text cells.
Extract Numbers or Text From Mixed Format Cells
Now we get to the hard part: getting numbers out of cells that contain multiple formats of input. If you have a number and a unit (like "7 shovels," as we have below), you'll run into this problem. To solve it, we're going to look at a couple different ways to split cells into numbers and text, letting you work with each individually.
Separate Numbers From Text
If you have a lot of cells that contain a mix of numbers and text or multiples of both, separating them manually might take a monumental amount of time. To get through the process faster, you can use Microsoft Excel's Text to Columns function.
Select the cells that you want to convert, go to Data > Text to Columns, and use the wizard to make sure the cells come out correctly. For the most part, you'll just need to click Next and Finish, but do make sure you pick a matching delimiter; in this example, a comma.
If you only have one- and two-digit numbers, the Fixed Width option can be useful too, as it will only split off the first two or three characters of the cell. You can even create a number of splits that way.
Note: Cells formatted as text will not automatically emerge with a number formatting (or vice versa), meaning you might still have to convert these cells as described above.
Extract a Number or Text From a Delimited String
This method is a bit cumbersome, but works very well on small datasets. What we assume here is that a space separates the number and text, though the method also works for any other delimiter.
The main function we'll be using here is LEFT, which returns the leftmost characters from a cell. As you can see in our dataset above, we have cells with one-, two-, and three-character numbers, so we'll need to return the leftmost one, two, or three characters from the cells. By combining LEFT with the SEARCH function, we can return everything to the left of the space. Here's the function:
=LEFT(A1, SEARCH(" ", A1, 1))This will return everything to the left of the space. Using the fill handleto apply the formula to the rest of the cells,this is what we get (you can see the formula in the function bar at the top of the image):
As you can see, we now have all the numbers isolated, so we can manipulate them. Want to isolate the text as well? We can use the RIGHT function in the same way:
=RIGHT(A1, LEN(A1)-SEARCH(" ", A1, 1))This returns X characters from the right side of the cell, where x is the total length of the cell minus the number of characters to the left of the space.
Now you can also manipulate the text. Want to combine them again? Just use the CONCATENATE function with all the cells as inputs:
=CONCATENATE(E1, F1)
Obviously, this method works best if you just have numbers and units, and nothing else. If you have other cell formats, you might have to get creative with formulas to get everything to work right. If you have a giant dataset, it'll be worth the time it takes to get the formula figured out!
Extract a Number From One End of a Continuous String
Now what if there's no delimiter separating your number and text?
If you're extracting the number from the left or right of the string, you can use a variation of the LEFT or RIGHT formula discussed above:
=LEFT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))=RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))This will return all numbers from the left or right of the string.
If you're extracting the number from the right of the string, you can also use a two-step process. First, determine the location of your first digit in the string using the MIN function. Then, you can feed that information into a variation of the RIGHT formula, to split your numbers from your texts.
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))=RIGHT(A1, LEN(A1)-B1+1)
Note: When you use these formulas, remember that you might have to adjust the column characters and cell numbers.
Extract Numbers From Both Ends of a Continuous String
With the strategies above, you should be able to extract numbers or text out of most mixed-format cells that are giving you trouble. Even if they don't, you can probably combine them with some powerful text functions included in Microsoft Excel to get the characters you're looking for. However, there are some much more complicated situations that call for more complicated solutions.
For example, I found a forum post where someone wanted to extract the numbers from a string like "45t*&65/", so that he would end up with "4565." Another poster gave the following formula as one way to do it:
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
To be completely honest, I have no idea how it works. But according to the forum post, it will take the numbers out of a complicated string of numbers and other characters. The point is that, with enough time, patience, and effort, you can extract numbers and text from just about anything! You just have to find the right resources.
After some more Excel tips? Here's how to copy formulas in Excel.
FAQs
How do I extract text and numbers from a cell in Excel? ›
Select all cells with the source strings. On the Extract tool's pane, select the Extract numbers radio button. Depending on whether you want the results to be formulas or values, select the Insert as formula box or leave it unselected (default).
How do I extract a specific text or string from a cell in Excel? ›- Use the LEFT, RIGHT and MID functions. You can use the LEFT, RIGHT and MID functions to extract specific text from a cell. ...
- Use the TRIM function. Sometimes, instead of wanting to remove text from a string, you want to remove spaces. ...
- Use the MID and FIND functions. ...
- Use Flash Fill.
Since we have done all the heavy lifting in the code itself, all you need to do is use the formula =GetNumeric(A2). This will instantly give you only the numeric part of the string.
What is the formula to extract text only from a cell? ›Save the code and close the window, then type this formula =TextOnly(A1) (A1 is the first row cell in your list range you want to extract text only from) into a blank cell, press Enter button, then fill the handle to the range you want.
How do I parse specific text in Excel? ›Click the “Data” tab in the ribbon, then look in the "Data Tools" group and click "Text to Columns." The "Convert Text to Columns Wizard" will appear. In step 1 of the wizard, choose “Delimited” > Click [Next]. A delimiter is the symbol or space which separates the data you wish to split.
How do I extract a specific value from a string? ›When you are working with data stored as a string, you can extract substrings from the total string. This extraction is done by specifying the offset within the string, indicating from which position you want to extract the substring. Position number from which to start extracting.
How do I extract a specific string? ›The substr() method extracts a part of a string. The substr() method begins at a specified position, and returns a specified number of characters. The substr() method does not change the original string. To extract characters from the end of the string, use a negative start position.
How do I extract text from a cell in Excel starting with characters? ›Summary. To extract words that begin with a specific character, you can use a formula based on six functions: TRIM, LEFT, SUBSTITUTE, MID, LEN, and REPT. This approach is useful if you need to extract things like a Twitter user name from a cell that contains other text.
How do I convert numbers to text in Excel automatically? ›On the Home tab, in the Number group, click the arrow next to the Number Format box, and then click Text. Note: If you don't see the Text option, use the scroll bar to scroll to the end of the list.
How do you separate character and numeric part of a string? ›- public class JavaTpoint{
- public static void main(String []args){
- String st = "JavaTpoint123456789";
- String st1=st.replaceAll("[^A-Za-z]", "");
- String st2=st.replaceAll("[^0-9]", "");
- System.out.println("String b = "+st1);
- System.out.println("Int c = "+st2);
- }
How do I extract only numbers from a cell in sheets? ›
We use the '' symbol so that Google Sheets understands that we mean the meta-character 'd' and not the letter 'd'. So if you want to extract a single numeric digit from a string, you use the expression 'd'. But if you want to extract more than one digits, you can use the expression 'd+'.
What is the formula for extracting text? ›For example, the formula =LEN() gives back the number of characters in a cell. So =LEFT(A1,LEN(A1)-2) extracts the entire text in a cell except the last two characters.
How do I extract specific data from a column in Excel? ›On the Excel Ribbon's Data tab, click the Advanced button. In the Advanced Filter dialog box, choose 'Copy to another location'. For the List range, select the column(s) from which you want to extract the unique values. Leave the Criteria Range blank.
How do you isolate data in Excel? ›Cell-Based Isolation
Isolate full cell values by applying conditional formatting rules. Click in the first cell to place in the format rule, hold down the left mouse button and drag to include the full range of cells.
Data parsing is converting data from one format to another. Widely used for data structuring, it is generally done to make the existing, often unstructured, unreadable data more comprehensible.
Which method is used to retrive string value of the particular? ›The valueOf() method returns the primitive value of a string.
How do I find a specific number in a string? ›To find whether a given string contains a number, convert it to a character array and find whether each character in the array is a digit using the isDigit() method of the Character class.
Which string function is used to retrieve specific part of the string? ›| Function | Description & Example |
|---|---|
| str-slice(string, start, end) | Extracts characters from string; start at start and end at end, and returns the slice. Example: str-slice("Hello world!", 2, 5) Result: "ello" |
- Click in the cell where you want to insert the function.
- Click the Formulas tab.
- Click the Text Function button.
- Select LEFT. ...
- In the Text field, select the cell containing text you want to extract.
- In the Num_chars field,enter the number of characters you want to extract.
- Click OK.
The difference between substring() and substr()
The two parameters of substr() are start and length , while for substring() , they are start and end . substr() 's start index will wrap to the end of the string if it is negative, while substring() will clamp it to 0 .
How do I extract data from an Excel cell before character? ›
Extract text before first space in Excel
To get text before a space in a string, just use the space character for the delimiter (" "). Since the instance_num argument is set to 1 by default, the formula will return text that appears before the first space.
Extract text before or after space with formula in Excel
You can quickly extract the text before space from the list only by using formula. Select a blank cell, and type this formula =LEFT(A1,(FIND(" ",A1,1)-1)) (A1 is the first cell of the list you want to extract text) , and press Enter button.
Select the column, or range where you'll be putting the values, then use CTRL+1 to bring up the Format > Cells dialog and on the Number tab select Text. Now Excel will keep your leading 0's. If you've already entered data and Excel has removed your leading 0's, you can use the TEXT function to add them back.
How to automatically insert text based on the value of another cell? ›Add text based on a cell value in VBA
Create a new module and enter the following formula. Type =CheckValue(A2) into cell B2 and autofill for the rest of the cells in the column. This is a great way to write such functions because it's more readable and can be easily modified.
We can generate Random Text Values or Letters from a list by using the RANDBETWEEN and CHOOSE functions. This step by step tutorial will assist all levels of Excel users in generating random values between two numbers.
How to extract only numbers from a string in shell script? ›- The first method we will look at to extract a number is to use the tr command. ...
- Another method is to use the sed command. ...
- In the next method we will use only a built in Bash function: $ STRING="The store is 12 miles away." ; echo "${STRING//[!
- charAt() To extract a single character from a String, you can refer directly to an individual character via the charAt() method. ...
- getChars() If you wish to extract over one character at a time, you'll be able to use the getChars() technique. ...
- getBytes() ...
- toCharArray()
The re. split() function splits the given string according to the occurrence of a particular character or pattern. Upon finding the pattern, this function returns the remaining characters from the string in a list.
How do I separate text and numbers in sheets? ›- At the top, click Data. Split text to columns.
- To change which character Sheets uses to split the data, next to "Separator" click the dropdown menu.
- To fix how your columns spread out after you split your text, click the menu next to "Separator" Detect automatically.
Select a Range of Cells
You can also select a cell range by selecting the first cell of the cell range, pressing and holding the Shift key and selecting the last cell of the cell range. The cell range is selected.
How do I extract specific data from sheets? ›
You can use LEFT + SEARCH in Google Sheets to extract text from a string or to extract data that comes before a specific text. LEFT is used to return a specific number of characters from the leftmost cell's beginning. SEARCH looks for specific characters/strings and determines their position.
How do I extract only certain values in Excel? ›Select a cell in the database. On the Excel Ribbon's Data tab, click the Advanced button. In the Advanced Filter dialog box, choose 'Copy to another location'. For the List range, select the column(s) from which you want to extract the unique values.
How do I extract part of a string between two characters in Excel? ›To extract part string between two different characters, you can do as this: Select a cell which you will place the result, type this formula =MID(LEFT(A1,FIND(">",A1)-1),FIND("<",A1)+1,LEN(A1)), and press Enter key. Note: A1 is the text cell, > and < are the two characters you want to extract string between.
How do I extract part of a string before a character in Excel? ›- Apply the =FIND(“,”, A1)-1 formula to return the proper length without delimiter.
- Extract the text before the given position using the LEFT function.
- Formula: =LEFT(A1, FIND(“,”, A1)-1)
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list. Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or formatting.
How do I remove all characters except numbers from a string? ›using filter() and lambda()
We will iterate over the string using the lambda() function and we will filter out the characters that are not digits using the filter() method and print the output string with only digits removing all other characters.
Use the String. replace() method to remove all non-numeric characters from a string. The String. replace() method will remove all characters except the numbers in the string by replacing them with empty strings.
How do you remove non numeric from a string? ›You can use the preg_replace function in PHP to remove all non-numeric characters from a string. The regular expression to match non-numeric characters is /[^0-9]/ , and you can use an empty string as the replacement.
What is the substring function in Excel? ›SUBSTRING Function in Excel. The SUBSTRING function is a pre-built integrated function in Excel that is categorized under the TEXT function. Substring means extracting a string from a combination of strings. For example, we have a string as “I am a Good Boy,” and we want to remove “Good” from the given string.
What is the trim function in Excel? ›Description. Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing. Important: The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text.
How to extract text before and after character in Excel formula? ›
The TEXTBEFORE function in Excel is specially designed to return the text that occurs before a given character or substring (delimiter). In case the delimiter appears in the cell multiple times, the function can return text before a specific occurrence.
How do I extract text after delimiter in Excel? ›Extract Excel Substring Using the Text to Columns Feature
This is another easy way to extract a substring in Excel. Just select the relevant data range and click on the Text to Columns button under the Data tab. Select the relevant delimiter and click OK in the Text to Columns wizard.
Extract text before or after space with formula in Excel
Select a blank cell, and type this formula =LEFT(A1,(FIND(" ",A1,1)-1)) (A1 is the first cell of the list you want to extract text) , and press Enter button.