1

Been stuck with something, probably stupid, but I don't know how to work around this. Could you please help - will appreciate that so much!

Below's a very simplistic scenario I tried to mimic from the original one I have at hand. Column A has the following data.

Col A
-----
Aaron
Billy
3
4
Chris
6

In the column B, I just want to find if a cell has text, if so, then I want to extract that using this simple formula: 

=IF(ISTEXT(A1),A1,"")

When I copy and paste this formula until the sixth row in column B, this is what I get:

Col A   Col B
-----   -----
Aaron   Aaron
Billy   Billy
3
4
Chris   Chris
6

Now I copy all the formulas in column B, and paste them as values in the same cell range. It seemingly appears that it has done the job, where there are blank values in rows 3, 4 and 6. But the problem is that I can't properly navigate using the CTRL + UP/DOWN keys anymore in column B. It just takes me to the very end, and the selection doesn't stop when it encounters a blank value.

Normally, when there is a blank cell within a column/row, and we try to do CTRL + ARROW keys, the selection stops at the cell just before the blank value. Why isn't it happening here? What do I need to do to resume this normal functioning?

Thank you in advance for the help!

Nik

Edit 1: Found a thread, that is almost similar to this one, and the discussion there is pretty extensive. The gist being that we can't have "empty" cells from formulas without the help from VBA.

Nik L
  • 39
  • 1
  • 6

1 Answers1

3

That's because column B doesn't have empty cells - there's either text or an empty string.

Empty string <> Empty cell.

And excel formulas can't return empty cell.

basic
  • 8,639
  • 2
  • 5
  • 23
  • Oh, I see - thanks for that information! So there's no way we can work around this (from UI that is)? – Nik L Jan 11 '20 at 13:38
  • It depends on what you want to achieve, if only to navigate, then think not. (without VBA) – basic Jan 11 '20 at 13:42
  • Yes, just for the purposes of navigation. Oh okay, I think it is a dead-end then - I will probably have to take the VBA route. – Nik L Jan 11 '20 at 13:44