How to Extract Number or Text From Excel

By | May 6, 2022

Microsoft Excel is great at working with numbers and text but if you use both in the same cell, you may run into some difficulties. Fortunately, you can extract numbers or text from cells to work with your data more efficiently. We demonstrate several options, depending on your current data format.

Excel Numbers Formatted As Text

This is a common situation, and fortunately very easy to deal with. Sometimes, cells containing only numbers are mislabeled 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 may also see a green flag in the upper-left corner of each cell.

Convert Text to Numbers in Excel

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

Otherwise, select the cell and, in the Number Format menu on the Ribbon, select an option Number defaults.

If you need more granular options, right click the highlighted cell and select Format Cells, which will open the respective menus. Here, you can customize the number format and add or remove decimals, increase the 1000 separator, or manage negative numbers.

Obviously, you can also use the Ribbon or Cell Format options described above to convert a number to text, or text to currency, time, or whatever other format you prefer.

Apply Number Formatting With Excel Paste Special

For this method to work, you must enter a number (any number) into the cell; it is important that these cells are also formatted as numbers. Copy that cell. Now, select all the cells you want to convert to number format, go to Home > Paste > Paste Special, choose Formats to paste only the cell format you copied initially, then click OK.

This operation applies the cell formatting you copied to all selected cells, even text cells.

Extract Number or Text From Mixed Format Cells

Now we come to the tricky part: extracting a number from a cell that contains many input formats. If you have numbers and units (like “7 of spades,” like we have below), you’ll run into this problem. To work around this, we’ll look at a few different ways to split cells into numbers and text, allowing you to work with each one individually.

Separate Numbers From Text

If you have a lot of cells that contain a mixture of numbers and text or multiples of both, separating them manually may take a lot of time. To go through the process faster, you can use Microsoft Excel’s Text to Columns function.

Select the cell you want to convert, go to Data > Text to Columns, and use the guides to make sure the cells exit correctly. For the most part, you just have to click Next and Finish, but make sure you select a suitable delimiter; in this example, comma.

If you only have one- and two-digit numbers, options Fixed Width can also be useful, as it will only divide the first two or three characters of the cell. You can even create a number of splits that way.

Notes: Cells formatted as text will not automatically appear with number formatting (or vice versa), meaning you may still have to convert these cells as described above.

Extract Number Or Text From Delimited String

This method is a bit complicated, but works very well on small datasets. What we’re assuming here is that a space separates the number and text, although this method also works for other delimiters.

The main function we’ll be using here is LEFT, which returns the leftmost character of the cell. As you can see in our dataset above, we have cells with one, two, and three character numbers, so we must return one, two, or three characters to the left of the cell. By combining LEFT with 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 handle to apply the formula to the entire cell, this is what we get (you can see the formula in the function bar at the top of the image):

Thanks For Visit

As you can see, we now have all the numbers isolated, so we can manipulate them. Want to isolate text too? We can use the function RIGHT in the same way:

=RIGHT(A1, LEN(A1)-SEARCH(" ", A1, 1))

This returns the character X 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 text. Want to put it together again? Just use the CONCATENATE function with all cells as input:

Obviously, this method works best if you only have numbers and units, and nothing else. If you have other cell formats, you may have to get creative with the formulas to get them all to work correctly. If you have a giant dataset, it will be well worth the time it takes to get to the known formulas!

Extract Number From One End of Continuous String

Now what if there is no delimiter separating your number and your text?

If you’re extracting numbers from the left or right of a string, you can use variations 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 numbers from the right of a 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 enter that information into a variation of the RIGHT formula, to separate the numbers from your text.

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

Catatan: Saat Anda menggunakan rumus ini, ingatlah bahwa Anda mungkin harus menyesuaikan karakter kolom dan nomor sel.

Ekstrak Bilangan Dari Kedua Ujung String Berkelanjutan

Dengan strategi di atas, Anda harus dapat mengekstraksi angka atau teks dari sebagian besar sel format campuran yang membuat Anda kesulitan. Meskipun tidak, Anda mungkin dapat menggabungkannya dengan beberapa fungsi teks yang kuat yang disertakan dalam Microsoft Excel untuk mendapatkan karakter yang Anda cari. Namun, ada beberapa situasi yang jauh lebih rumit yang membutuhkan solusi yang lebih rumit.

Misalnya, admin menemukan posting di forum di mana seseorang ingin mengekstraksi angka-angka dari string seperti “45t * & 65 /”, sehingga ia akan berakhir dengan “4565.” Poster lain memberikan rumus berikut sebagai cara untuk melakukannya:
=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)

Honestly, I don’t know how it works. But according to the forum post, it will output a number from a complex set of numbers and other characters. The bottom line is, with enough time, patience and effort, you can extract numbers and text from just about anything!

Category: