I have a large spreadsheet of data which stubbornly will not change data type. when I run the type() formula on a cell, it returns the value 1, and it will not change no matter what I have tried.
This is a problem because I cannot use the data in formula (the data is numerical and wont workin vlookup forumlas)
Is there any way to FORCE these cells to become data type = 1? i.e. type(cell)= 1?
43 Answers
Given your now stated problem with VLOOKUP, I would recommend changing the text values that look like numbers, to numbers. One way to do this:
- Select a blank cell
- Edit/Copy
- Select the range to be converted to numbers
- Edit/Paste Special / Add
This solution did not work for me on my spreadsheet so I had to use some formulas to accomplish the task. I used the following get the text converted to a number and then copied it back to the column.
Example:
_$_2100.00
=NUMBERVALUE(MID(E65,2,10))
I could not just use =NUMBERVALUE(E65) because it returned a #VALUE! error because the place that I got the numbers from had a leading space in the cell so I had to use the Mid() function to deal with the leading space. BTW the TRIM() function would not work either.
Just something else to try if the first option doesn't work.
Highlight the cells/column, click on Text to Columns, remove any delimiters that could split up the cell, and select the data type. It will force change the data type for that column
1
