Clicking in cell has strange effect

[ExcelLearner25]

I have a column of numbers that was converted from a PDF. The cells are formatted as text and are a little messy so I have a formula that uses a RegEx add-in to convert the imported data to a nice clean number. It works except for a few cells and I can't figure out why. When it doesn't work, the cell with the formula is blank. I'm pretty sure the RegEx is OK. I've tried TRIM and CLEAN to get rid of any unwanted characters but that doesn't help. I've tried changing the format of the offending cell, cut and pasting it to values only, and many other things I've forgotten. But the only thing that works is to click in the problematic cell (or formula bar). Selecting the cell isn't enough--I have to get the cursor active in there--then click anywhere else in the worksheet. Then the desired result magically appears.

Can anyone tell me why? What does clicking in the cell do?

 

[xlhelp15]

I believe your problem statement - few cells are still remains as text instead of numbers, right? If that is the case, try the below one.

1. Type 1 in any of the blank cell and Copy (CTRL+C) it.
2. Select your dataset that you wish to convert.
3. Press ALT - E - S then you would see the "Paste Special" option.
4. Select the opreation as "Mulitply" and then click OK.

Now, all of your text formatted numbers will be converted to Numbers format.

 

[ExcelLearner25]

That did it, thanks.

 

[xlhelp15]

Glad we helped - Please make it as solved to close the conversation and thanks for your feedback!!!