Excel Guides

Converting Text to Numbers in Excel

You can convert text to numbers in Excel using the VALUE function. This function takes a text value as an argument and returns a number. For example, the following formula returns the number 3:

=VALUE("3")

If you have a column of text values that you want to convert to numbers, you can use the VALUE function in an array formula. An array formula is a formula that performs calculations on more than one value at a time. To enter an array formula, you press CTRL+SHIFT+ENTER instead of just ENTER when you finish the formula.

For example, suppose you have a column of text values in cells A1:A5, as shown in the following figure. These values were entered as text, not numbers, so they don't sort correctly.

To convert these text values to numbers, you can use the following array formula:

=VALUE(A1:A5)

Note:

  • Be sure to enter this formula as an array formula by pressing CTRL+SHIFT+ENTER.

  • The VALUE function converts only the first character of each cell to a number. If the first character in a cell can't be converted to a number (for example, if it's a letter), VALUE, returns the #VALUE! error value.

Convert text to numbers by using the TEXTTONUMBER. . .function

  1. Convert Text to Numbers by Using the TEXTTONUMBER Function

The TEXTTONUMBER. . .function converts a txt string into a number

  • (syntax): =TEXT(value, format_text)

Value (required): The value to be converted to number Format_text (optional): The format code from https://support.office.com/enus/article/formattingcodesfortextandnumbersusedinconnectionsandqueriesinmicrosoftquery70321ed7 -1034 - 4af5 - b07e - 9ec1db28f361?ui=enUS&rs=enUS&ad=US that defines how value should be converted to text

"For example , if your data is in cells A1 : A5 and you want to convert it into numeric values in cells B1 : B5 , you can use this TEXTTONUMBER function : = TEXT ( A1 : A5 , "# , ##0 " )." (Microsoft Corporation , Redmond , WA ) " (Microsoft Corporation , Redmond , WA ) " (Microsoft Corporation , Redmond , WA ) " (Microsoft Corporation , Redmond , WA )

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.