Excel Data Format Issue - Can't convert text to "general" or "numbers."

Question

I'm trying to copy-paste these figures into an excel worksheet:

https://electionresults.ewashtenaw.org/electionreporting/nov2020/canvassreport1.html

The issue I'm finding is that once they are copied over, it's not recognizing them as numbers, so I can not sum them or do anything else with them that I'm needing to do for my project. I've tried "clearning" formats. I've tried doing "Text to column" to each column. I've tried changing them "general" format to "numbers" format and back to "general." None of this works to any avail.

How do I format the figures to get them to act as general numbers?

Answer 1

Hi,

Thanks for sending over the file via DropBox. I discovered that the reason the figures in your spreadsheet weren't recognised as numbers and consequently weren't summing was because there was a space in front of each number in your data set.

As such, I applied the formula highlighted below to remove the space before each number as displayed in columns N to X below with the total in column Y to ensure they were all summing correctly. The formula does the following:

(1) Takes the characters to the right of the cell. The full length of the cell from the right is taken using the LEN formula with the RIGHT formula minus 1 to remove the space regarded as a character at the start of the cell.

(2) The VALUE formula is then wrapped around the RIGHT formula to convert the result into a numeric value.

You can choose to paste the newly formatted data over the original data. I also tried this with your data set and it worked fine.

All the best,

Judith

Answer 2

Sure, no problem.

The LEN formula works out the total number of characters in a cell;

The RIGHT formula extracts characters from another cell starting from the last character in the cell i.e. the rightmost character. The number of characters to be extracted is based on the number you specify in the formula e.g. if cell A2 has alphanumeric data for instance and we wanted to extract only the numbers from the data within it which has CNB1579, we could write the formula =RIGHT(A2,4) in another cell so that the last 4 characters in the rightmost part of the string i.e. 1579 of A2 will be extracted. If the formula was =RIGHT(A2,3), we would extract 579 and if the formula was =RIGHT(A2,2), we would extract 79 etc.

If we use B2 as an example from your dataset, the data displayed is [Space]829. The space is counted as a character so the LEN formula calculates that B2 has 4 characters. As you want to extract everything except the space in the RIGHT formula we deduct 1 from the full number of characters the cell contains so the space at the start of the cell is not extracted hence the expression in the formula LEN(B2)-1. In this instance, this returns the number 3.

Essentially, the formula =RIGHT(B2,LEN(B2)-1) is the same as saying =RIGHT(B2,3). However, we use the LEN formula in this instance instead of just writing 3 to make the formula dynamic because you have various numbers in your data set of varying lengths to which we apply this formula. As such, if we were to write =RIGHT(B19,3) to extract the 1279 from cell B19 in your dataset, we would only get back 279 instead of the full number which would be incorrect. As such, the use of LEN in conjunction with RIGHT lets Excel consider the full character length of each cell when extracting the correct number of characters. =RIGHT(B19,LEN(B19)-1) in this instance is understood in the context of B19 as =RIGHT(B19,4) i.e. the full length of the cell being 5 minus 1 for the space.

I hope this helps.

Let me know if you need further information.

With kind regards,

Judith

Answer 3

So, on the very first step, I did =VALUE(B2) and got this error:

"Error in Value A value used in the formula is of the wrong data type."

I've got figures in columns B to L, spanning B2 to L151. You all can try to copy and paste the data into a sheet to see what's wrong with it.

Answer 4

The error message you're seeing indicates that the data in cell B2 might contain a non-numerical character or the cell might be blank. Try the following steps to identify the problematic cell:

Select the range of cells B2:L151. Click the "Data" tab in the ribbon, then click "Text to Columns". In the Text to Columns Wizard, select "Delimited" and click "Next". In the next step, make sure all delimiter options are unchecked and click "Next". In the third step, select "General" for column data format and click "Finish".

If there are any cells in the range that cannot be converted to numbers, you should see an error message indicating which cells contain errors.

Answer 5

Hi

I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself.

It's possible that the data is formatted as text, preventing Excel from recognizing it as numerical data. One way to resolve this issue is to use the VALUE function to convert the text into numbers. Here's how:

In a blank cell, enter the formula =VALUE(cell), where "cell" is the cell containing the text you want to convert to a number. For example, if the text is in cell A1, the formula would be =VALUE(A1).

Press enter to apply the formula. The cell should now display the numerical value of the text.

Copy the formula and paste it into the cells containing the text you want to convert to numbers.

Highlight all the cells containing the newly converted numerical values and click on the "Number Format" dropdown menu in the "Home" tab. Select the format you want to use (such as "General" or "Number") to format the cells as numerical values.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.