Excel not recognizing cell values

Viewed 40

In the process of pulling data from an audit, the platform returns most of the data as a "true/false" format. Specifically it's all lowercase, and aligned to the left with no spaces before or after and no obvious formatting outside of that. However, when trying to use formulas that identify or count based on the value of the cell, such as COUNTIF, excel will not recognize the value of the cell. Used ISTEXT to see if it's a value or formatting issue and it will return a TRUE result. Used ISLOGICAL to check if it recognized the "true/false" value and got a false result. However, if i click into the cell string (as if to edit the text) and then press enter, the cell will reformat, capitalizing all font, centering the cell, and changing the values of the cell checks. ISTEXT becomes false, ISLOGICAL becomes true, and the cell is able to be interacted with by if and count functions. Any ideas why this is? And any solutions to address this? i'd rather not go through 200+ cells manually fixing it.

1 Answers

A few things to try:

  1. Use F9 or Control+Alt+Shift+F9 to refresh the workbook.
  2. Highlight the entire dataset and try to set the cell formatting from text to general
  3. Highlight Data -> Data Tab -> Text To Columns -> Finish
Related