Text in cells but no text visible

[SimonHughes]

Hello, I have a download which I am trying to format and one task is to delete all rows with empty cells. Problem is, that there are cells which look empty but an ISTEXT query comes up as TRUE. It is not a font format issue and hitting F2 places the cursor at the point 1 in the cell.

Any ideas how I can delete the rows where there is "invisible" text? TIA

New AR 17.07.2024.xlsx
	A	B
2	NAME	ISTEXT
3	Site:	TRUE
4	Test Date	TRUE
5	Engineer Name	TRUE
6	Engineer Signature	TRUE
7	Damper Reference	TRUE
8	Level	TRUE
9	Location	TRUE
10	Damper Type	TRUE
11	Damper Size	TRUE
12	Duct Size	TRUE
13	Damper Manufacturer	TRUE
14	Installation Checks	TRUE
15	Installed to specification	TRUE
16	Access to damper	TRUE
17	Builders Hatch Size (if required)	TRUE
18	Firestopping integrity acceptable?	TRUE
19	Installation Pass/Fail	TRUE
20	Functional Testing	TRUE
21	FD Tested	TRUE
22	Pass, Fail, Pass with issues	TRUE
23	Damper Cleaned	TRUE
24		FALSE
25		TRUE
26		TRUE
27		FALSE
page 1 (3)

Cell Formulas
Range	Formula
B3:B27	B3	=ISTEXT(A3)

 

[hagia_sofia]

What does

=CODE(A25)

return?

 

[SimonHughes]

Also, there are 21 rows that I wish to keep (from and including "Site" to "Damper Cleaned") and everything between "Damper Cleaned" and "Site" can be deleted. However, the number of rows between these two values are variable, it could be 10 rows or 17 rows that need deleting. So how do I go about deleting an unknown number of rows between two know conditions - many thanks

 

[Joe4]

Please answer hagia_sofia's question.
That will tell us exactly what Excel sees in those apparent blank cells.

 

[Alex Blakenburg]

If =Code(A25) returns 63 can you also please try =Unicode(A25)

 

[felixstraube]

Can you maybe share a copy of the file since the XL2BB code does not copy the invisible text:

This is what i get with code:

Book1
	A	B
1
2	NAME	ISTEXT
3	Site:	TRUE
4	Test Date	TRUE
5	Engineer Name	TRUE
6	Engineer Signature	TRUE
7	Damper Reference	TRUE
8	Level	TRUE
9	Location	TRUE
10	Damper Type	TRUE
11	Damper Size	TRUE
12	Duct Size	TRUE
13	Damper Manufacturer	TRUE
14	Installation Checks	TRUE
15	Installed to specification	TRUE
16	Access to damper	TRUE
17	Builders Hatch Size (if required)	TRUE
18	Firestopping integrity acceptable?	TRUE
19	Installation Pass/Fail	TRUE
20	Functional Testing	TRUE
21	FD Tested	TRUE
22	Pass, Fail, Pass with issues	TRUE
23	Damper Cleaned	TRUE
24		FALSE
25		FALSE
26		FALSE
27		FALSE
Sheet1

Cell Formulas
Range	Formula
B3:B27	B3	=ISTEXT(A3)

 

[SimonHughes]

What does

=CODE(A25)

return?

This is the answer. It returns a numeric where there is valid text and #VALUE where there is none. I can now sort by CODE (after paste values), delete #VALUE and sort back to original. Excellent, many thanks hagia_sofia!