Save as CSV omitting formulae with null cells

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
974
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 ""

SubjectStart DateStart Time
End DateEnd TimeAll Day EventDescriptionLocationPrivate
4 - Wasters Anonymous - Save it session 13/4/20191:00:00 PM3/7/20194:30:00 PMFALSEMonday Big 12 83 Y https://www.HoardersRUs.comHere 22 Acacia Avenue Nowhere OK 62020FALSE
3 - Wasters Anonymous - Save it session 23/14/20191:00:00 PM3/17/20194:30:00 PMFALSEThursday Small 12 83 N https://www.HoardersRUs.comThere 22 Acacia Avenue Nowhere OK 62020FALSE
7 - Wasters Anonymous - Save it Refresher3/24/20191:00:00 PM3/27/20194:30:00 PMFALSESunday Medium 12 83 Y https://www.HoardersRUs.comHere 22 Acacia Avenue Nowhere OK 62020FALSE
2 - Savers & Hoarders - Cleaning out Day3/27/20191:00:00 PM3/30/20194:30:00 PMFALSEWednesday Medium 18 83 Y https://www.HoardersRUs.comHere 22 Acacia Avenue Nowhere OK 62020FALSE
2 - Savers & Hoarders - Cleaning out Night4/4/2019FALSE Medium 18 83 N https://www.HoardersRUs.comThere 22 Acacia Avenue Nowhere OK 62020FALSE

<tbody>
</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?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows
Maybe try using VBA instead to only copy over the rows you want to export (instead of trying to do it with formulas).
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,965
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top