How to extract numbers or text from Excel

Microsoft Excel is great for working with both numbers and text, but if you use both in the same cell, you may have some difficulties. Fortunately, you can extract numbers or text from cells to work with your data more efficiently.

How to extract numbers or text from Excel?

  1. Excel numbers are formatted as text
    1. Convert text to numbers in Excel
    2. Apply number format with Excel's Paste Special feature
  2. Extract numbers or text from mixed format cells
    1. Separate numbers from text
    2. Extract a number or text from a delimiter string
    3. Extract numbers from one end of a continuous string
    4. Extract numbers from both ends of a continuous string

Excel numbers are formatted as text

This is a common situation, and fortunately, very easy to handle. Sometimes, cells only contain numbers that are labeled or incorrectly formatted as text, preventing Microsoft Excel from using them in operations.

As you can see in the picture below, the cells in column A are formatted as text. You can also see a green flag in the top left corner of each cell.

How to extract numbers or text from Excel Picture 1

Convert text to numbers in Excel

If you see a green flag in the top left corner, select one or more cells, click the warning sign and choose Convert to Number .

Or, select the cells and, in the Number Format menu on the Ribbon, select the default Number option.

If you need more detailed options, right-click on the highlighted cell (s) and select Format Cells , the corresponding menu will open. Here, you can customize the number format and add or remove decimals, etc.

Apply number format with Excel's Paste Special feature

For this method to work, you will need to enter a number (any number) into a cell. It is important that this cell is also formatted as a number. Copy that cell. Now, select all the cells you want to convert to a number format, go to Home> Paste> Paste Special , select Formats to paste only the format of the original copied cell, and then click OK.

Extract numbers or text from mixed format cells

Now, we'll get to the harder part: Get numbers out of cells that contain multiple input formats. If there is a number attached to the unit, you will encounter this problem. To solve it, consider a few different ways to split cells into numbers and text.

Separate numbers from text

If you have multiple cells containing a mixture of numbers and text, splitting them manually can take a long time. However, you can use the Text to Columns function of Microsoft Excel.

Select the cells you want to convert, go to Data> Text to Columns and use the wizard to make sure the cells appear correctly. For the most part, you'll only need to click Next and Finish , but make sure you select an appropriate delimiter; In this example, it is a comma.

If only numbers have 1 and 2 digits, the Fixed Width option may also be useful, as it will only separate the first two or three characters of the cell. You can even create some splits that way.

Note : Cells formatted as text will not automatically appear as a number format (or vice versa), meaning that you still have to convert these cells as described above.

Extract a number or text from a delimiter string

This method is a bit complicated, but works great on small data sets. The main function to use here is LEFT, which returns the leftmost characters from a cell. Refer to the article: LEFT function, how to use the function to cut the left string in Excel to know how to do it.

If you want to split the text, you can use the RIGHT function the same way. Refer to the article: RIGHT function, how to use the function to cut the right string in Excel for more details.

Extract numbers from one end of a continuous string

What if there is no separator between numbers and text?

If extracting numbers 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 command will return all numbers from the left or right of the string.

How to extract numbers or text from Excel Picture 2

If extracting numbers from the right of the string, you can also use the two-step process. First, determine the position of the first digit in the string with the MIN function . You can then use that information as a variable for the RIGHT formula, to separate numbers from text.

 =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) =RIGHT(A1, LEN(A1)-B1+1) 

Note : When using these formulas, keep in mind that you may have to adjust column characters and cell numbers.

Extract numbers from both ends of a continuous string

With the above methods, you will be able to extract numbers or text in most mixed-format cells. Additionally, you can combine them with some of the powerful text functions included in Microsoft Excel to get the characters you are looking for. However, there are some complex situations that require more complex solutions.

4.7 ★ | 3 Vote

May be interested

  • How to use the MAXA function in Excel, detailed examplesHow to use the MAXA function in Excel, detailed examples
    the maxa function in excel helps find the largest value in a data set, including numbers, logical values, and text containing numbers. this is different from the max function, which only counts numbers. let's learn the syntax and usage of the maxa function with practical examples.
  • Split numbers from strings in ExcelSplit numbers from strings in Excel
    in the process of working with excel, i have encountered some cases that require separating only the fraction in a given string, now this post shares with you how to do this. the following article details how to separate numbers from a given string in excel.
  • Instructions for exporting text in photos using AI with extreme accuracyInstructions for exporting text in photos using AI with extreme accuracy
    there are many tools to export text in images, and now ai technology has also been used to export text in images with higher accuracy, the quality of text is also guaranteed to be more stable with the online imgocr tool.
  • MINA function in Excel, how to use and examplesMINA function in Excel, how to use and examples
    the mina function in excel helps determine the smallest value in a range of numbers, including logical values ​​and text numbers. with simple syntax, mina supports optimizing the calculation process in spreadsheets.
  • How to create Text Box in ExcelHow to create Text Box in Excel
    text box on excel is similar to creating on word, helping you create artistic text frames, attracting content viewers.
  • How to format numbers in ExcelHow to format numbers in Excel
    how to format numbers in excel in the process of working, processing data in excel sometimes you need to format numbers in excel so that data can display the number format in accordance with the requirements to be processed. you do not know how to format numbers in ex
  • How to extract a file or folder from a TAR or TAR.GZ fileHow to extract a file or folder from a TAR or TAR.GZ file
    a tarball (tar or tar.gz) is a collection of files grouped together as a single archive that makes it easy to store and transfer large numbers of files locally or over the internet. whenever you need files, you just need to unzip the archive.
  • How to extract and copy text from photos taken on iPhoneHow to extract and copy text from photos taken on iPhone
    today's technological developments can allow you to directly extract text from images taken with a smartphone camera.
  • How to convert negative numbers to positive numbers ExcelHow to convert negative numbers to positive numbers Excel
    to convert negative numbers to positive numbers and vice versa on excel, you only need to perform a very simple operation.
  • How to convert Apple Numbers documents to Microsoft ExcelHow to convert Apple Numbers documents to Microsoft Excel
    numbers is spreadsheet software specifically for apple devices both on phones and on computers. basically, apple numbers works similar to microsoft excel, so you can completely convert documents between these two toolkits to serve each specific working situation.