How to Delete "Blank" Cells En Masse So They Are Truly Empty?

[chasfh]

I have come across a problem in which I want the "then" part of a formula to be completely empty.

Here's an example of what I mean. The actual formula here is not important—just the end part of it is:

=IFNA(XLOOKUP($DI2+(EE$5-1901),$G$4:$AK$4,$G1:$AK1),"")

The very end part of this formula is asking to make the cell "blank" if the cell referred to is NA. I may apply this formula to thousands of cells within an array. Afterwards, to make the worksheet nimble, I will copy/paste values so I still see the number that the formula wrought in the appropriate cells, without having to keep the formula in the cell. Any cells without data in them will be "blank". Or at least look empty.

The problem is, any cell I ask to be made blank is not truly empty, but instead has an apostrophe, quote mark, or caret in the cell (depending on cell alignment).

This is fine as far as dependent calculations on the cell are concerned, but the problem comes when I am trying to sort a column of a few thousand rows that has a few hundred of these "blank" cells in them. The sort function reads these cells as being numerically higher than any cell with an actual number in it, so when sorting Z-A, they end up at the top of the column, which creates an obvious problem.

If I can change the status of the cells en masse from "blank" to empty, that will solve the sort problem, but I have not yet found a simple way to do this en masse. The Find and Replace function (CTRL+H) doesn't help because when I try to find any of these "blank" cells, by pressing the space bar in the Find field, the function returns "We couldn't find anything to replace." Obviously, searching for ', ", or ^ doesn't work. And of course, not pressing the space bar in the Find field doesn't even trigger any recognition by the function that I'm trying to use it.

Does anyone recognize this problem and can help? I know I can just go to a "blank" cell, click delete, and that will empty the cell, but I don't want to have to manually find and select every "blank"cell so I can do that. I would rather select an entire array of cells that has both data and "blank" cells in them, and empty out just the blank cells.

 

[awoohaw]

1. One way, and you'd have to do it for each column. Is to sort so that you have all the "" values together and then delete the values in the cells them (but that is destroying column formula integrity which we've already discussed).

If you are comfortable with VBA you could do one of two routines.
1. Do the formula for that cell in VBA and pass the value into the cells in that column. That way blanks will populate and your problem is solved, but the formula is in VBA and not in the column.
2. To have a macro go down the column and delete any cell with a value of "".

 

[MARK858]

Rather than Copy/Paste values try using the code

Range(your range).Value = Range(your range).Value

 

[johnnyL]

Rather than Copy/Paste values try using the code

Range(your range).Value = Range(your range).Value

@MARK858 just an FYI, from my experience, that is only quicker up to a certain amount of cells, about 5000 cells as I recall.

 

[MARK858]

@MARK858 just an FYI, from my experience, that is only quicker up to a certain amount of cells, about 5000 cells as I recall.

Not doing it for speed, I am suggesting it for making the cells truly blank

 

[johnnyL]

You referring to:
.value = .value
?

 

[MARK858]

.value = .value?

Yes it converts empty strings differently to Copy/Paste Values

 

[johnnyL]

I got ya,

 

[awoohaw]

@MARK858 just an FYI, from my experience, that is only quicker up to a certain amount of cells, about 5000 cells as I recall.

if it is the only train in the station to your desitination, you got to get on it.

 

[chasfh]

Here is the mini-sheet. The blank cells have the alignment character in them. Is there any way to make those cells truly empty when I create and paste a formula, if the cell reference is also empty, blank, or otherwise NA?

SAMPLE BLANK EMPTY CELL QUESTION.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Name	1901	1902	1903	1904	1905	1906	1907	1908	1909	1910	1911	1912	1913	1914	1915	1916	1917	1918	1919	1920	1921	1922	1923	Averages
2	José Abreu	128	129	121	121	132	132
3	Bill Abstein	75	72	74	90	91	74
4	Jerry Adair	68
5	Buster Adams	75	74	75	75	85	108	119	118	102	87														110
6	Russ Adams	82	70	69	74	71
7	Joe Adcock	101	115	124	135	145	141	129	133	135	131	122	118	121	125	140									134
8	Jesús Aguilar	108	112
9	Willie Aikens	126	124	125	128	128	113	80
10	Eddie Ainsmith	69	64	65	70	87	97	85	87	90	77	70													89
11	Gary Alexander	83	76
12	Manny Alexander	74	75	71	58	60	65	66	59	58	67	75	74	72	68	65									59
13	Edgardo Alfonzo	131	118	111	103	93	80	55
14	Albert Almora	98	87	72	61	58
15	Brian Anderson	67	76	79
16	Garret Anderson	109	95	95	101	97	83	60
17	Ed Andrews	97	102	100	92	83	73	63
18	Elvis Andrus	81	90	91	83	79	85	100	101	85	75	72	72												93
19	Nori Aoki	103	106	105	101
20	Jimmy Archer	71	68	71	80	84	86	90	89	80	74	73	69	68											84
21	Oswaldo Arcia	105	93	78
22	Nolan Arenado	126	130	131	126	111	108
23	Hank Arft	90	83	90	91	84
24	Joaquin Arias	67	78	86	78	71	63
25	Randy Arozarena	75	90	132	131
26	Richie Ashburn	127	133	121	119	119	107	104	108	114															111
27	Bob Aspromonte	75	75	71	65	85	87	88	92	86	87	94	83	68	63	64									89
28	Doug Ault	75	66
29	Jeff Bagwell	153	171	164	163	165	163	162	154	144	137	130	121	112											149
30	Neal Ball	88	76	65
31	Turner Barber	91	95	99	79
32	Darwin Barney	74	77	71	64	68	84	78	68																68
33	John Barry	78	90	102	96	94	104	104	92	79															85
34	Kevin Bass	104	96	103	113	106	84
35	Frank Baumholtz	91	78	84	100	105	102	94	85	74	71														79
36	Matt Beaty	93	107
37	Clyde Beck	60	50	57
38	Erve Beck	99	98
39	Mark Bellhorn	103	115	106	101	92	73	70
40	Cody Bellinger	135	143	117	63
41	Josh Bell	113	112
42	Carlos Beltrán	75	95	88	91	111	122	128	121	116	127	130	129	132	134	138	133	122	113	115	111	93			119
43	Mike Benjamin	71	67	66	68	64	45
44	Todd Benzinger	83	91	88	79
45	Johnny Berardino	91	77	75	77	82	85	82	66	64	63	58													65
46	Marty Bergen	69	73	71
47	Dave Bergman	82	84	88	97	107	122	107	83	83	114	114	108	108	99	80									83
48	Ken Berry	82	82	91	105	99	81	69
49	Sean Berry	121	117	113	108	78	53
50	Yuniesky Betancourt	86	87	80	73	76	76	67	58																58
51	Wilson Betemit	126	115	103	85
52	Steve Bieser	76	76
53	Craig Biggio	105	101	87	75
54	Larry Biittner	76	92	87
55	Lena Blackburne	75	75	75	75	75	75	76
56	John Boccabella	71	72	57	51	41
57	Brennan Boesch	62	49
58	Don Bollweg	104	96	76
59	Skye Bolt	66	55
60	Julio Borbon	77	72	71	73	75	75
61	Rob Bowen	80	66
62	Jack Boyle	83	71	70	72	66
63	Jackie Bradley	55	64	100	111	97	89	93	82	53															67
64	Darren Bragg	76	91	77	63
65	Jeff Branson	83	61	56	68	73	73
66	Kitty Bransfield	88	93	98	114	106	85	77
67	Jack Brohamer	94	89	72	68	70
68	Lew Brown	124	119	90	84	79	84	93
69	William Brown	77	84	84	81	91	96	86
70	Eric Bruntlett	81	78	76	61	48
71	Bill Bruton	99	106	106	100	101	102	101
72	John Buck	84	83	97	104	91	81	81	80																80
73	George Burns	81
74	Homer Bush	68	68	72
75	Sal Butera	62
76	Enos Cabell	111	99	82
77	Asdrúbal Cabrera	112	109	98	96	105	114	114	108	102	93	86													105
78	Willie Calhoun	83	94	84	81
79	Johan Camargo	104	87	65	65
80	Vin Campbell	127	105	91	104	120	120
81	Jeimer Candelario	98	90	90	113	123
82	John Cangelosi	100	86	76	74	75	77	96	123	108	88	82	79												116
83	Andy Carey	112	92	81	97	120	103	85	89	95															92
84	Fred Carisch	75	75	70	71	68
85	Chico Carrasquel	88	93	88	84	89	81	70
86	Bill Carrigan	71	91	106	102	98	97	100	105	89	85														97
87	Fred Carroll	134	135	136	143	125	102
88	Frank Carswell	76
89	Joe Carter	112	111	110	102	98	111	118	111	100	90	85	82	85											105
90	Mike Caruso	75	60	56	72	67
91	Alberto Castillo	52	49	60	73	77	73	71	71																71
92	Jose Castillo	66	69
93	Juan Castro	67	58	47	50	58	59	64
94	Starlin Castro	92	92
95	Chris Chambliss	112	114	101	87	86	81	74
96	Bob Chance	78	73
97	Sam Chapman	102	104	101	103	106	96	81
98	Endy Chavez	86	73	75	83	86	65	71	88																88
99	Raul Chavez	74	76	66	49	48	62	70	67	65															66
100	Cupid Childs	148	138	138	138	125	118	128	129	115	97	87	89												122
101	Harry Chiti	84	79	69	61	65	78	97	86	65	60	65													75
102	Archi Cianfrocco	79	79	86	96	84	64
103	Alex Cintron	94	79	72	72	66	68	66
104	Bud Clancy	74
105	Brady Clark	75	81	81	84	92	107	105	93	87	78														90
106	Horace Clarke	83	88	82	71
107	Andy Cohen	84	79
108	Darnell Coles	75	72	64	96	106	100	97	86	68	75	94	89	76	72	76	77								77
109	#N/A
110	Dave Collins	83	107	111	105	90	95	104	95	89	88	73	71	74											92
111	Jimmy Collins	114	99	84
112	Joe Collins	80	105	124	131	128	122	107	84	72															78
113	Lou Collier	58	66	78	78	78	75	75	78																78
114	Pat Collins	70	98	128	123	102	83
115	Tyler Colvin	83	90	86	70
116	Jeff Conine	97	107	112	107	105	106	100	88	80															84
117	Roger Connor	155	152	146	146	145	134	126	121	109	96														115
118	Bill Conroy	75	75	74	74	77	78
119	Willson Contreras	124	116	111	115	115	111
120	Ron Coomer	80	79	81	82	79	75
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=ISFORMULA(C1)	text	NO
C1	Expression	=ISFORMULA(C1)	text	NO
A2:C646	Expression	=ISFORMULA(A2)	text	NO
D2:Z646	Expression	=ISFORMULA(D2)	text	NO
D2:Z646	Cell Value	top 2% bottom values	text	NO
D2:Z646	Cell Value	top 1% values	text	NO
D2:Z646	Other Type	Color scale		NO

 

[Alex Blakenburg]

As @MARK858 pointed out the only way you can do that is with a macro.
If you are happy to select the area first then running this macro will fix it.

Sub ConvertFormulasToValues()

    Selection.Value = Selection.Value

End Sub