Forum Discussion
Silvabod
Aug 23, 2023Copper Contributor
Find and Replace with blank - How to, please?
One column of data 40000+ cells. About 850 of these need to be blank. Using Find 0 and replace with " " resulted in 850 cells containing " " Instead of being empty, as intended. Using Find " " rep...
- Feb 10, 2022
OK, that suggests that the values are text values.
With such a value in A2, enter the following formula in the cell next to it:
=LET(v,A2,p,FIND(" ",v),d,LEFT(v,p-1),s_1,FIND("/",d),s_2,FIND("/",d,s_1+1),t,MID(v,p+1,15),DATE(RIGHT(d,4),LEFT(d,s_1-1),MID(d,s_1+1,s_2-s_1-1))+TIMEVALUE(t))
Fill down.
Or use Power Query (Get and Transform).
HansVogelaar
Aug 23, 2023MVP
Leave the 'Replace with' box empty, and make sure that you tick the check box 'Match entire cell contents', then click 'Replace All'.