How to convert text to number in Excel

The tutorial shows many different ways to turn a string into a number in Excel: Convert to Number error checking option, formulas, mathematic operations, Paste Special, and more.

Sometimes values in your Excel worksheets look like numbers, but they don't add up, don't multiply and produce errors in formulas. A common reason for this is numbers formatted as text. In many cases Microsoft Excel is smart enough to convert numerical strings imported from other programs to numbers automatically. But sometimes numbers are left formatted as text causing multiple issues in your spreadsheets. This tutorial will teach you how to convert strings to "true" numbers.

How to identify numbers formatted as text in Excel

Excel has an inbuilt error checking feature that alerts you about possible problems with cell values. This appears as a small green triangle in the top left corner of a cell. Selecting a cell with an error indicator displays a caution sign with the yellow exclamation point (please see the screenshot below). Put the mouse pointer over the sign, and Excel will inform you about the potential issue: The number in this cell is formatted as text or preceded by an apostrophe.
Number formatted as text in Excel.

In some cases, an error indicator does not show up for numbers formatted as text. But there are other visual indicators of text-numbers:

Numbers Strings (text values)
  • Right-aligned by default.
  • If several cells are selected, the Status Bar shows Average, Count and SUM.
  • Left-aligned by default.
  • If several cells are selected, the Status Bar only shows Count.
  • The Number Format box displays the Text format (in many cases, but not always).
  • There may be a leading apostrophe visible in the formula bar.

In the image below, you can see the text representations of numbers on the right and actual numbers on the left:
The text representations of numbers and actual numbers in Excel

How to convert text to number in Excel

There are a handful of different ways to change text to number of Excel. Below we will cover them all beginning with the fastest and easiest ones. If the easy techniques don't work for you, please don't get disheartened. There is no challenge that cannot be overcome. You will just have to try other ways.

Convert to number in Excel with error checking

If your cells display an error indicator (green triangle in the top left corner), converting text strings to numbers is a two-click thing:

  1. Select all the cells containing numbers formatted as text.
  2. Click the warning sign and select Convert to Number.

Excel's Convert to Number error checking feature

Convert text into number by changing the cell format

Another quick way to convert numerical values formatted as text to numbers is this:

  1. Select the cells with text-formatted numbers.
  2. On the Home tab, in the Number group, choose General or Number from the Number Format drop-down list.

Convert text to number by changing the format.

Note. This method does not work in some scenarios. For example, if you apply the Text format to a cell, enter a number, and then change the cell format to Number, the cell will remain formatted as text.

Change text to number with Paste Special

Compared to the previous techniques, this method of converting text to number requires a few more steps, but works almost 100% of time.

To fix numbers formatted as text with Paste Special, here's what you do:

  1. Select the text-number cells and set their format to General as explained above.
  2. Copy a blank cell. For this, either select a cell and press Ctrl + C or right-click and choose Copy from the context menu.
  3. Select the cells you want to convert to numbers, right-click, and then click Paste Special. Alternatively, press the Ctrl + Alt + V shortcut.
  4. In the Paste Special dialog box, select Values in the Paste section and Add in the Operation section.
  5. Click OK.

Change text to number with Paste Special.

If done correctly, your values will change the default alignment from left to right, meaning Excel now perceives them as numbers.

Convert string to number with Text to Columns

It is another formula-free way to convert text to number in Excel. When used for other purposes, for example to split cells, the Text to Columns wizard is a multi-step process. To perform the text to number conversion, you click the Finish button in the very first step :)

  1. Select the cells you'd like to convert to numbers, and make sure their format is set to General.
  2. Switch to the Data tab, Data Tools group, and click the Text to Columns button.
  3. In step 1 of the Convert Text to Columns Wizard, select Delimited under Original data type, and click Finish.

That's all there is to it!

Convert string to number with Text to Columns.

Convert text to number with a formula

So far, we have discussed the built-in features that can be used to change text to number in Excel. In many situations, a conversion can be done even faster by using a formula.

Formula 1. Convert string to number in Excel

Microsoft Excel has a special function to convert a string to number - the VALUE function. The function accepts both a text string enclosed in quotation marks and a reference to a cell containing the text to be converted.

The VALUE function can even recognize a number surrounded by some "extra" characters - it's what none of the previous methods can do.

For example, a VALUE formula recognizes a number typed with a currency symbol and a thousand separator:



To convert a column of text values, you enter the formula in the first cell, and drag the fill handle to copy the formula down the column:
VALUE formula to convert a string to a number.

For more information, please see VALUE formula to convert text to number.

Formula 2. Convert string to date

Apart from text-numbers, the VALUE function can also convert dates represented by text strings.

For example:




Where A2 contains a text-date.

By default, a VALUE formula returns a serial number representing the date in the internal Excel system. For the result to appear as an actual date, you just have to apply the Date format to the formula cell.
VALUE formula to change a text string to a date.

The same result can be achieved by using the DATEVALUE function:


For more information, please see How to convert text to date in Excel.

Formula 3. Extract number from string

The VALUE function also comes in handy when you extract a number from a text string by using one of the Text functions such as LEFT, RIGHT and MID.

For example, to get the last 3 characters from a text string in A2 and return the result as a number, use this formula:


The screenshot below shows our convert text to number formula in action:
Extract a number from a text string.

If you don't wrap the RIGHT function into VALUE, the result will be returned as text, more precisely a numeric string, which makes any calculations with the extracted values impossible.

For more information, please see How to extract number from string in Excel.

Change Excel string to number with mathematic operations

One more easy way to convert a text value to number in Excel is to perform a simple arithmetic operation that does not actually change the original value. What can that be? For example, adding a zero, multiplying or dividing by 1.




If the original values are formatted as text, Excel may automatically apply the Text format to the results too. You may notice that by the left-aligned numbers in the formula cells. To fix this, be sure to set the General format for the formula cells.
Converting a string to a number with mathematic operations

Tip. If you'd like to have the results as values, not formulas, use the Paste Special feature to replace formulas with their values.

That's how you convert text to number in Excel with formulas and built-in features. I thank you for reading and hope to see you on our blog next week!

You may also be interested in

154 comments to "How to convert text to number in Excel"

  1. RC says:

    How can I convert a number (I get it from data from WEB) to Pivot table? Number in data is not appearing in pivot table. I used convert text to columns wizard and it work but every time I refresh the data, I need to go to convert text to columns wizard again.

  2. Muataz says:

    Great explanation, thank you

    I encounter this problem with different way, I downlaoded a sheet that includes column "Budget", the cells in this colum are formatted as currency and it is left aligned, and the status bar shows only count. I tried to convert it to numbe by using Value function but it gives me error, the only way that worked with me, is selecting the cells and replace $ to blank, this makes the figures inside the cells numbers.
    Is there any explanation for this?

  3. SR says:

    This has been such a mightmare. I finally got the following to work. Following all your suggestions and more, I just replace-all'd elimination of "$" and "." and ".00" to get 2, 3, 4, and 5 digit numbers that stubbornly refused to go to numeric. I extracted from the left the correct number of digits -- eg, 50 wants =let(cell,2) -- then I had to =trim(new cell) **even though there is no apparent space anywhere in sight**. Still it's not numeric but at that point you can multiply by 1 and turn it into a freaking number: yay!!!

    You cannot imagine how long it has taken to get this to work.

    And you have to manually reset the number of digits to extract. extracting more than the visible digits and then triming, just doesn't work. (@@) go figure.

  4. Tommy G says:

    Hello, I'm trying to erase a space before a number (for thousands of cells which I got from another file) but I can't do it:
    Example: a cell contains a space before a number 1, and I need it to be just number 1, without a space before it.
    The problem with the number 1 after a space is that excel consider it a a text, the SUM of it is 0.
    When I overwrite a 1 in that cell which contains a space before the number 1, it converts to a number and SUM operates normally; but I can't do it cell by cell for thousands of cells.
    Please help me.

      • Tommy G says:

        Dear Alexander, thank you very much for your kind answer.
        I tried Paste Special, but when I copied the cell it copied only those which were numbers already, and didn't copy the other "numbers" which doesn't work as numbers.

        When I look at the numbers in the columns I notice that the same number is a little bit (one space) to the left of the "so called number" (which cannot be added, etc)
        If I look at that cell in particular, which contains for instance 145 it has a space before the number,
        but if I replace that writing the number 145 over it, will not have any space before it and then it will work fine as a number.

        Can't understand what kind of format those so called numbers have.

    • SD says:

      Do a 'find and replace' and put a space character in the find field and nothing in the replace field. It will apply to everything though....

    • Akpor says:

      Hello Tommy,
      Please try the TRIM function. (TDLR, google how to use TRIM)
      For example, if the column containing the numbers is A, and the first cell with data is A2 [not counting the header row], you should:
      1. Create (Insert a new column (B in our example)
      2 . In column B2, type: TRIM(A2)
      [this will give the output of the content of the content of cell A2 without any leading or trailing spaces]
      3. You can then fill the formula down by double-clicking the fill handle or any other way.
      [this will make the entire column B the same as A without any leading spaces]
      4. You can then copy column B and paste it in A as VALUES using the PASTE SPECIAL.

      I hope this helps.

  5. Crestina Perez says:

    I am trying to convert Satisfaction surveys from text to numbers, to avoid manual converting for receiving a high number of surveys in a weekly. I was provided with the following formula with little to no information on how to add it on the exported repot. There is a main sheet, but the data needs to be add once the exported sheet is completed. The formula provided is below and I am trying to convert only one column from text to numbers.
    =IF(T3489="Extremely dissatisfied",1,IF(T3489="Somewhat dissatisfied",2,IF(T3489="Neither satisfied nor dissatisfied",3,IF(T3489="Somewhat satisfied",4,IF(T3489="Extremely satisfied",5,0)))))

    Hoping to hear back!

  6. WAYNE says:

    I have a list of names in excel which im looking to formulate into a list of numbers
    All names are differnt but im looking them to change into number formatt 1-8

  7. James says:

    I have non numerical data (responded in monkey survey) the responses are in word i.e. agree, strongly agree, disagree, strongly disagree for many questions. I have exported the data of the survey to Excel sheet and all the responses are in word (string). I need to analyses basic statics corelating it with one variable (like male/female respondents). How do I convert this strings to numeric for analysis in excel?.

  8. Dr Vaibhav Pandir says:

    I have

    Like this data with me i want to convert this string in to number or unique numbers how to convert please help me

  9. Ken Boyle says:

    I have cells containing a simple subtraction like 12-9. It needs to display like that so I have applied Text formatting to it. The cell next to it needs to display the calculation - in this case 3. Excel converts it to a date and displays 44451. The 12-9 needs to be changeable and not require reformatting after data entry. Hope you can help - thanks

  10. Mico says:

    I am trying to convert a value with dollar sign in text to Numeric but Value Function is giving error instead of giving 400 & 500 as results. Excel doc also says this should work. What may be the probable reasons.
    I am posting the exact code below

    A B C
    1. Name Money Won Value
    3. Mico $400 #VALUE! #=VALUE(B3)
    4. Noah $500 #VALUE! #=VALUE(B4)

  11. Veta says:

    Dear all, thank you for all these tips but it is not working for me. I need to change numbers stored as text into general format in order to be able to use a VLOOKUP formula. When I change my text into general I notice that the value change as well. For instance, it changes 92753660758293 into 92753660758290... How can I keep the right value? Many thanks for your answer.

  12. Harikrishnan R says:

    A1= 1
    A2 = (1+5)/2 as text
    A3 = (2+4)/2 as text
    in A4 i need the formulated A1*A2*A3
    A2 & A3 only can be write as text type

  13. Rick Welsh says:

    I have a large file with a column of data where I want to convert a 5 digit/character code alphanumeric text to number. Your instruction handles most of these cells in my file, but not for instances with an embedded alpha "E" character in the string. This column has 5-character codes that I wish to convert to number for a vlookup application. An example: a code of 71E62. I cannot get that cell (and many others) to convert to a number.

  14. Lyndsay says:

    I am still battling with this:
    There are a number of tricks you can use if you have a column of numbers in a format that Excel does not recognise as numbers. For example, let's say you have a column of numbers where some idiot has typed each number in using a comma as the thousands separator like this:


    If your version of Excel does not use commas for the thousands separator, (mine is et to use the apostrophe - ' -), or if some dummy actually typed in the commas, you can still quickly convert the entire column of text-numbers to numbers by using the Data - Text to columns function:

    Highlight the entire column.
    Click text to numbers
    Select "Delimited" and hit next.
    Uncheck all the delimiters and hit next
    Click General and then Advanced
    In the advanced box it asks for the decimals separator and for the thousands separator.
    Enter the separator you see on the screen (NOT the separator that you want). So in the above example you would put a comma as the thousands separator, and a full-stop as the decimal separator.
    click OK and finish.

    It works !!! - until the "text number" is in 1000's
    ie: R35,06 text converts to R35.06 number and
    R128,71 text converts to R128.71 number

    BUT -
    R4 020,40 text says as is and does not convert

  15. neil says:


    how to Change above string to numbers? thanks

  16. Shashwath says:

    Hello All,

    I have data with 10 digit followed by X. I need a digit in place of X.

    Example: 0123456789X is the data and i need the output to be like 01234567890, 01234567891,.......0123456789

  17. ROBERTO M. LIWANAG says:

    how do I convert all text strings to numbers in a column in Excel sheet
    Thank you.

  18. Kristen M Wissmar says:

    none of these worked for me. I have just numbers copied from powerpoint into excel. I've used, trim, value, left, text to columns, and just simply changing it, and none of these are working.

  19. Annatjie says:

    I work on spreadsheets daily, but all this needed info is tiresome!!
    I need a way where I can type minimum and it says it all...
    Like for instance: a column has different things like; interested, not interested, phone tomorrow, phone June, etc.
    Lazy me wants to connect each to a number and by pressing that number, it will say "Interested" or how ever it may be...
    I also need to know how to get the one column to add automatically say three days for follow-up call or to highlight the June call, that needs to be made, appear more urgent as we approach June, for instance...

    Any possible way to assist, please?

    Thank you kindly

  20. Barry says:

    Hi. How do I get a column of written data selections (unlimited rows but 10 or 12 choices, eg different pathology types, to be converted to nunbers. Say, infection = 1, trauma = 2 etc.....

  21. Anthony says:

    I have a column of numbers that have an "AUD" reference at the end of each number. As a result it is not recognising them as numbers and I cannot tally the column. How do I remove the "AUD" reference from each cell to enable it to be added as numbers?

  22. Kelum says:

    use below view for more clarity
    .................................... Total
    1 20 0 10 20 20
    2 25 25 20 10 25
    3 15 25 8 40
    4 40 200 10 30 40
    5 10 100 20 10 110
    6 30 100 6 20 30
    7 5 100 10 10 5
    8 0 100 8 20 0
    9 20 100 8 20
    10 25 200 0 30 25

  23. Kelum says:

    I have to get total of each row in below. I used "auto sum " to get the result, but it gets wrong value for each raw. How can i fix this, pls. help
    1 20 0 10 20 20
    2 25 25 20 10 25
    3 15 25 8 40
    4 40 200 10 30 40
    5 10 100 20 10 110
    6 30 100 6 20 30
    7 5 100 10 10 5
    8 0 100 8 20 0
    9 20 100 8 20
    10 25 200 0 30 25

    • Hello!
      To replace a letter with its number in the alphabet, use the formula

      =CONCAT(IFERROR(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1), IF(CODE(MID(A1,ROW($1:$93),1))-64<=0, MID(A1,ROW($1:$93),1), CODE(MID(A1,ROW($1:$93),1))-64)),""),"")

      This is the formula for uppercase letters. If you need to replace lowercase letters, then instead of 64 use 96 in the formula

  24. Ellen says:

    How do I recode a drop-down list in Excel, in other words assign a code number for each of the drop down responses? I've tried several of the options but they haven't worked.

  25. Charalampos Vichos says:

    I have
    AA 8881/17
    BR 078/16
    BR 078/18
    BR 078/19
    CZ 458/18
    EK 148/15
    EK 148/15
    EK 9287/18
    KL 0427/15
    KL 0427/18
    and i need only the last 2 digits or to be sorted from the last 2 digits how can I do it?

  26. girraj says:

    How we can data in number when some data copied with table and pasted in excel now that numbers are in text and before starting numbers many spaces are there
    how we can do convert such cases in number format

  27. bobi says:

    I cannot use vlookup formula when numbers are presented as text.
    Example I have:
    and I need to have the values as text, so I can sort them A to Z. I always get the error massage, when use vlookup formula.
    Any help?

  28. Mary says:

    I have a column on the left that goes from 0-60. The column on the right contains a formula that reads: =COUNTIF(CY$4:CY$66,0
    I put $ so that those inputs don't change but I'd like for the 0 to change to 1, 2 and so on.
    So going down the column it should look like this:
    And so on.
    That way I don't have to go into each cell to change each number individually. The reason I have to change it is because I have to add and delete rows which causes for there to be a gap which I then have to fix. I'm sure there's a way. Please help!

    • I recommend using the ROW function as a counter.
      If your formula


      is in cell D2 (i.e., line 2) then change the formula


      After that you can copy this formula down along the column.
      Hope this is what you need.

  29. Judith Bass says:

    Hi all.
    Need help on formula.
    In cell A2, there is 4 digit no. "1234"; cell A3, "2345".
    In cell B2, there is a text "M" and cell B3 text "C".
    How to form a formula in cell C2 and C3 that the output are "MM-181234-01" and "CC-182345-01" respectively.

  30. fran says:

    I have a number 328,345 in General format
    Somehow I looked some of the hidden characters it looks like "328,345 " notice the space
    I used clean, trim and value in all the nine orders but to no avail can i get that number to allow me to do any number functions like add.
    Any ideas

  31. Peterclaver says:

    Please, kindly suggest how to convert all text strings to numbers in a column in Excel sheet? Example:
    I have tried all the suggestions stated above but it still doesn't recognize it as a number when I want to calculate or find the maximum of some numbers and it is included.

  32. Enisa says:

    I'm working with Geodesy and obviously excel is a part of it since i insert my points in measurement device. So in one of the columns i have list of numbers 145,987 etc. and it appears to be correctly written number, until i select that cell and in the function bar it appears 145987. I've tried changeing to text, customize format cells general, even entered formulas, and in options changed that thousands be marked as , and I manage to change number with a comma 145,987 but in function bar it says 145987.

    Could you please help me.
    Thank you,

    • Hello Enisa!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail.
      What exactly do you enter in the cell - 145987 or 145,987 or 145 987? Do you want to change cell format (appearance) or cell content?
      It’ll help me understand it better and find a solution for you. Thank you.

  33. Jay says:

    Hey everyone anyone help me my some project. How a number replace in to next column.
    Column 1 1234567
    Column 2 Your Sr xxxxxxx will be closed against column 1 number.i want to show a number in column 2 where xxxxx will be shown.
    Which formula used in excel

    • Hello Jay!
      If I understand your task correctly, the following formula should work for you:

      ="Your Sr xxxxxxx will be closed against "&A1&" number"


      =CONCATENATE("Your Sr xxxxxxx will be closed against ",A1," number")

      I hope it’ll be helpful.

  34. Michael P. says:

    IMPORTANT!!! Please, review the method for converting Text to Numbers ("Change text to number with Paste Special"). I believe your instruction "Paste Special" should be followed by "Formats" and not by "Values". Otherwise the existing data in the selected cells will be lost....
    With all due respect,
    Keep up the good work,

    • Hello Michael,

      Thank you for your feedback. The instructions are correct. The idea is to convert numbers stored as text to numbers by performing an arithmetic operation, addition in this case. For this, you add an empty cell (i.e. zero) to the copied Values, not Formats.

  35. Angela says:

    How do I convert a number stored as text (1 hour 30 minutes 45 seconds) to time format (01:30:45) in Excel?

    • Hello Angela!
      Please try the following formula:

      =TIME(LEFT(A1, SEARCH(" ", A1)-1), MID(A1, SEARCH(" ", A1, 3)+1, SEARCH(" ", A1, SEARCH(" ", A1, 3)+1)-SEARCH(" ", A1, 3)),MID(A1, SEARCH(" ", A1, 15)+1, SEARCH(" ", A1, SEARCH(" ", A1, 15)+1)-SEARCH(" ", A1, 15)))

  36. Sarah says:

    New to excel, but wondering if there is a way to convert a place value for a point score (sporting event spreadsheet) across multiple cells
    Eg. 1st place(1) = 4 points, 2nd = 3, 3rd =2 & 4th =1
    So that when you type 1 it automatically converts to 4 etc

  37. Jose says:

    Thanks for the tips, they were very helpful, easy to follow and you provided more than one way to complete the task at hand. Big KUDOS to you

  38. kamleshwar says:

    I am unable to convert these into numbers as well as unable to sum of them. Kindly give solution.

  39. deepak says:

    How to rectify the below scenario?
    Offshore team, excel format in column A is populated as text, but when onshore opens the sent excel, in their machine the column A appears to be in Number format.

  40. Ibukun says:

    your tip really helped me on how to change text number to number, as I have been on it for days, i initially thought my excel wasn't working. This has been helpful. Thanks

  41. Paulo says:

    I want to separate this number in 2 columns, breaking it where the comma is...


    The result I want to obtain is 25 and 10. But when I convert it I get 25 and 1.
    what am I doing wrong?


    • Deepak says:

      Select the cell u wanted to convert>Data>Text to columns>select delimited and click next>select comma from the delimiters column and click on finish.

  42. Marcin says:

    I have pasted a large number of values which have "." as a separator (example 23.310
    ). I need to convert this to either "," (example 23,310) or no separator at all. I have tried formatting the cell to currency, general and few other just to get a value to do calculations and it doesn't work. Is there a command which would replace all "." to ","?

    Thanks in advance.

    • Ricardo says:

      Hi Marcin.
      Same problem here. Some logs have 23.310 instead of 23,310 and import them to excel doesn't change the format.

      A very quick solution that worked for me:
      - Choose the column with mouse
      - Use replace function -> replace . for , -> it's text so it's works with no problem
      - If necessary format the column to number after it replacing

  43. Keshav says:

    I want to change the text into number by following way:
    abc = (1+2+3) = 6
    bcd = (2+3+4) = 9
    cde = (3+4+5) = 12

    Pls Help. Thank You in Advance.

  44. Donna says:

    How to create a sum field that totals columns that were converted to numeric via Data >Tools> Text to Columns. Sum function not recognizing values in columns converted as numeric, even with format changed to numeric.

  45. Georgina says:

    In order to use Regression in the data analysis I need to have numeric data. However some of it is blank using "" in a previous formula, but this is recognised as text. Do you have any suggestions as to what I can put instead of "" which represents blank but is not text?

    NULL would also be text of course...

  46. Steph says:

    Is there a way to do the following: I have a row and in it, it has Single and Family mixed all the way through. I want it to basically calculate how many Single cells there are but a single cell is a certain cost like $50. The same with Family, how many family but the price for family is $100. So it won't actually tell me there are 4 single and 8 family, it would give me the total price tag.

  47. Joseph Ang says:

    How do i change from a qualitative data (Beginning/Developing/Achieving/Exceeding) to a quantitative data (1/2/3/4pts) when a person key in this in google excel:
    Beginning = 1pt
    Developing = 2pts
    Achieving = 3pts
    Exceeding = 4pts

  48. AUDRA says:

    Can I formulate a word, example Estate to equal 30 days so in the spread sheet when the word estate is used it will calculate 30 days from a specific date in a cell?

  49. Parag says:

    So, I have a form created to enter data and the form is linked to excel. The issue I am having is that when someone enters the amount on the form it gets saved as text on Excel. This form is an ongoing process so is there a way to automate the Excel to convert all entries in the Amount column to Number? Instead of converting them everytime manually?

  50. sarah guy says:

    I want ton convert the following to from the following format
    Fri Aug 23 13:05: 39 2019 to dd/mm/yyyy format Is this possible??

  51. Sebplus says:

    I have the following challenge, when generating a string that I'm then willing to reference a named variable. Imagine 4 cells in a sheet, such as:
    A1 contains the number 9, and I name that cell Blue.Color using Formula/Define Name
    A2 contains the number 6, and I name that cell Green.Color
    B1 contains the text Green
    B2 contains a formula such as =A2&".Color"
    The problem is that B2 cell now shows Green.Color, whereas I would have wanted to read 6
    Any trick for me ?
    Thanks a lot,

  52. Ric Edward Gaspar says:

    the value of A1 is "31-35", how can I convert this to (4)? When I use =Value(a1) the result is #value!

  53. BH says:

    A5 consists of a set of values "3,8,5,6,12" collected from other formulas.
    This set of values will have to be used by CHOOSE frequently, CHOOSE(x,A5) where x is a variable.
    But, "CHOOSE(x,A5)" returns "#VALUE!".
    How can I make it work, please? Thank you very much.

  54. guy says:

    Finally, Someone who actually knows what they are doing. All of the articles I found including MS only give you one or two options. The Paste Special function is the first time I've seen this method and it did the trick for me. Great article, well organized, comprehensive, and easy to follow. Thank you

  55. Daan says:

    I want to convert a placement to an other number, like: placement 1 = 10, placement 2-5 = 7.
    What is the best way to do this?
    thanks in advantage!

  56. Sakandar says:

    How to convert a value number of 3.5581E +14 to the right number. it is not working in with format cells. Thanks for responding.

  57. Kevin Jernigan says:

    I need to give the letter P (for present) the value of 1 and the letter A (for absent) the value of 0. Very new to Excel and can't figure it out. Thank you.

      • Bharath says:

        Hi, Just Select all, then Ctrl+F, at top select "Replace", then in find what give "1", and give "P" in Replace with and right below select "options " and select only "match entire cell content" and select "Replace all" in left bottom

  58. RAVI says:

    Kindly suggest how to convert all text strings to numbers in a column in Excel sheet? Example:

    • Aksana (Ablebits Team) says:

      It seems to me that the easiest solution is to apply the ‘Extract’ tool offered by ‘Ablebits Ultimate Suite’. One of the options its menu has is ‘Extract numbers’. Simply click the corresponding radio button on selecting your records, and a new column will appear next to the original one. The extracted data will get numerical status for Excel and lose commas, by the way.


    Kindly suggest how to convert all text strings to numbers in a column in Excel sheet? Example:
    Thanks. RAKESH

  60. Roger says:

    The other problem might be that the 'System Separator' is not the same as your pasted value.
    I.e. In the cell you have value 100.22 In other words 100 point 22 or 100 decimal 22.
    If your File -> Options -> Advanced -> (About 16 lines down) Use system separator is ticked then the decimal point will make the field text - No matter what you do.
    To correct this, un-tick 'Use system separators' and ensure that 'Decimal separator' is a . and 'Thousands separator' is empty and not a blank.

    • goulag says:

      Thanks to you, column to text was causing excel to crash, this was the best solution since i am trying to automate the a script, that does just this.

  61. Paul says:


    My excel does not recognize the following as a value 2 027,60. I need a thousands separator to be a "," and decimal to be "." . I have a whole column of values that need to be converted.

    Please help!!!

    I have tried the substitute function which works on changing the decimal function for values less than a thousand, thereafter it does not work on a value of 1,000 or more.


  62. trickyt57 says:

    There are a number of tricks you can use if you have a column of numbers in a format that Excel does not recognise as numbers. For example, let's say you have a column of numbers where some idiot has typed each number in using a comma as the thousands separator like this:


    If your version of Excel does not use commas for the thousands separator, (mine is et to use the apostrophe - ' -), or if some dummy actually typed in the commas, you can still quickly convert the entire column of text-numbers to numbers by using the Data - Text to columns function:

    Highlight the entire column.
    Click text to numbers
    Select "Delimited" and hit next.
    Uncheck all the delimiters and hit next
    Click General and then Advanced
    In the advanced box it asks for the decimals separator and for the thousands separator.
    Enter the separator you see on the screen (NOT the separator that you want). So in the above example you would put a comma as the thousands separator, and a full-stop as the decimal separator.
    click OK and finish.

    Hey presto your text-numbers now ARE numbers in your preferred Excel format. (with an apostrophe as the thousands separator - in my case)

  63. Zeshan Ahmed says:

    I want to add text data field in rows portion after Values field in excel pivot table. How can I do that?

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)