Share via

Trouble with formatting Number Stored as Text data to Number format.

Anonymous
2021-09-03T15:33:33+00:00

I have a column of numbers retrieved from a db I do not control. When the datasheet is downloaded one of the columns which should be formatted as a number arrives as Number Stored as Text.

I have tried to reformat the column as number however for some reason it does not reformat. I have to use the error handler to convert the data to number.

I would like to have the data converted to number when I copy the original imported data into a new worksheet.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2021-09-04T00:22:31+00:00

    Hi Rob Ingles,

    As per your description, I understand you already reformat the column as number, but it still doesn’t work.

    Since, “choose Convert to Number” option doesn’t work for you. **** There is many another way if a number or set of numbers in a column or row is formatted as text in Excel.

    Using The VALUE Function:

    There is a special function in Excel that’ll convert a number formatted as text into a numeric value. This is the VALUE function. To use this function, select the cell where you want the converted number to go and type:=VALUE(A2)

    Replace “A2” above with the cell that has the number you want to convert. Press Enter and you’ll see that the text-formatted number has been converted to a General-format number. 

    ![Image](https://learn-attachment.microsoft.com/api/attachments/683a4392-d1c6-4345-a648-91f30569846a?platform=QnA"2">

  2. Select Data from the menu, and then select Text to Columns in the Data Tools section of the ribbon.

3.In the Wizard window, keep the default Delimited selected and select Next

4.On the next Wizard page, keep the default Tab selected, and select Next again.

  1. Finally, on the last page of the Wizard, make sure General is selected under Column data format. For the Destination field, you can either select a new column where you want the number data to go, or just keep the current selected column as is. Select Finish

Image

Now your data will all be converted to numeric values, which you can use in Excel formulas and calculations.

Meanwhile, if you still unable to convert and if you don’t mind and if it is convenient for you, could you please send me a copy of the document files to me so that I can look from my side, and I will help you to fix it for you from my side.

If there is confidential information, then you can send it to me in Private Message

I appreciate your understanding and stay safe!!

Best Regards

Waqas Muhammad

0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-09-04T01:30:12+00:00

    try doing a:

    TRIM(SUBSTITUE(A1,CHAR(160),))

    to remove any spaces (visible or non visible)

    then convert to number

    0 comments
  2. Anonymous
    2021-09-07T19:10:47+00:00

    Hi Rob Ingles,

    May I know if you have update information regarding to this issue? Feel free to post back if you need further assistance.

    Best Regards,

    Waqas Muhammad

    0 comments
  3. Anonymous
    2021-09-07T19:36:27+00:00

    Hi,

    You might also try these:

    1. Click an empty cell and choose Copy. Select the bad data and press Alt+Ctrl+V, and double-click Add.
    2. =--RIGHT(A1,LEN(A1)-1)

    Shane

    0 comments