how to remove blank columns and replace it with the value of the next non-blank cell in the row

[mrfrost]

Hi,
I need to format a data set for a mail merge. In essence I am looking for help on how to remove blank columns and replace it with the value of the next non-blank cell in the row (without the use of Macros).

Blank values are allowed in the Widget columns are allowed
Each email address will have no more than 20 Widgets associated with it

Example Data set:
Email | Widget 1 | Widget 2 | Widget 3 ... up to Widget 200
xyz@gmail.com | a | | | | | | b
pqr@gmail.com | | | | c | d | e | | | | | | | | f

Required Outcome:
xyz@ | a | b
pqr@ | c | d | e | f


Thanks,

 

[Peter_SSs]

In essence I am looking for help on how to remove blank columns and replace it with the value of the next non-blank cell in the row (without the use of Macros).

Try this with a copy of your worksheet
1. Select any single cell on the worksheet
2. F5 -> Special... -> Blanks -> OK
3. Right click anywhere on the grey cells - Delete .. -> Shift cells left -> OK