Save as CSV omitting formulae with null cells

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
I have a daily extract file which may have zero to 100 entries. I want to import into my Google Calendar as a CSV but it needs reformatting into the calendar import format so I did that on another tab using formulae like
=IF(ISBLANK(CSV!B2),"",CSV!B2&" - "&CSV!C2&" - "&CSV!D2)

The results look good with nothing appearing where the formulae results in null ""


<tbody>
[TD="class: xl65"]Subject[/TD]
[TD="class: xl65, width: 85"]Start Date[/TD]
[TD="class: xl65, width: 105"]Start Time
[/TD]
[TD="class: xl65, width: 69"]End Date[/TD]
[TD="class: xl65, width: 91"]End Time[/TD]
[TD="class: xl65, width: 85"]All Day Event[/TD]
[TD="class: xl65, width: 268"]Description[/TD]
[TD="class: xl65, width: 161"]Location[/TD]
[TD="class: xl65, width: 48"]Private[/TD]

[TD="class: xl65"]4 - Wasters Anonymous - Save it session 1[/TD]
[TD="class: xl66, align: right"]3/4/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/7/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Monday Big 12 83 Y https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]Here 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]

[TD="class: xl65"]3 - Wasters Anonymous - Save it session 2[/TD]
[TD="class: xl66, align: right"]3/14/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/17/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Thursday Small 12 83 N https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]There 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]

[TD="class: xl65"]7 - Wasters Anonymous - Save it Refresher[/TD]
[TD="class: xl66, align: right"]3/24/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/27/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Sunday Medium 12 83 Y https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]Here 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]

[TD="class: xl65"]2 - Savers & Hoarders - Cleaning out Day[/TD]
[TD="class: xl66, align: right"]3/27/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/30/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Wednesday Medium 18 83 Y https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]Here 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]

[TD="class: xl65"]2 - Savers & Hoarders - Cleaning out Night[/TD]
[TD="class: xl66, align: right"]4/4/2019[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"] Medium 18 83 N https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]There 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

</tbody>


...but when I Save As CSV it fails on the import as all the rows with null results are exported with the comma delimiters:
Subject,Start Date,Start Time,End Date,End Time,All Day Event,Description,Location,Private
4 - Wasters Anonymous - Save it session 1,3/4/2019,1:00:00 PM,3/7/2019,4:30:00 PM,FALSE,Monday Big 12 83 Y https://www.HoardersRUs.com ,Here 22 Acacia Avenue Nowhere OK 62020,FALSE
3 - Wasters Anonymous - Save it session 2,3/14/2019,1:00:00 PM,3/17/2019,4:30:00 PM,FALSE,Thursday Small 12 83 N https://www.HoardersRUs.com ,There 22 Acacia Avenue Nowhere OK 62020,FALSE
7 - Wasters Anonymous - Save it Refresher,3/24/2019,1:00:00 PM,3/27/2019,4:30:00 PM,FALSE,Sunday Medium 12 83 Y https://www.HoardersRUs.com ,Here 22 Acacia Avenue Nowhere OK 62020,FALSE
2 - Savers & Hoarders - Cleaning out Day,3/27/2019,1:00:00 PM,3/30/2019,4:30:00 PM,FALSE,Wednesday Medium 18 83 Y https://www.HoardersRUs.com ,Here 22 Acacia Avenue Nowhere OK 62020,FALSE
2 - Savers & Hoarders - Cleaning out Night,4/4/2019,,,,FALSE, Medium 18 83 N https://www.HoardersRUs.com ,There 22 Acacia Avenue Nowhere OK 62020,FALSE
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,

How do I save as a CSV but omitting the rows where the result is null without manually deleting rows in Excel or Notepad?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Maybe try using VBA instead to only copy over the rows you want to export (instead of trying to do it with formulas).
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top