You are here:

Excel/Excel Macro To Edit Layout + Save To New Worksheet




I am looking for help on an issue.

I'm pretty new to Excel/macros.

Currently I have an excel file containing 1 sheet that looks like this.

*Unedited Jpg*

I have been asked to do the follow.


The file our process picked up from [Removed Directory] contained:

(**Data from spreadsheet pasted in a .txt file)

021030879       CIF     T992000927      AFE
022774509       CIF     METAFLAKE       AFE
023478761       CIF     T993001236      AFE
025713660       NULL    5473539361133832        CCC
055607444       CIF     T025000110      AFE
056197015       CIF     T316000006002   AFE
056530934       CIF     T028000054001   AFE
52550191        NULL    052833639       CIF

There are a few things wrong with this file.

1 - No header record which should contain row count and checksum
2 - It is meant to be comma delimited but no commas in the file.
3 - Source system values shown as NULL when should be CIF in this case.

I have created a file that will process and looks like:


Should not have the NULL values in source but format correct.

The original spreadsheet had a way of generating the checksum in each row (field 5) and is summed up in field 2 of header. I just added any number to get it to work.
The first field of the header contains the row count of the rows in the file.
The code stops processing when the checksum in field 5 is zero indicating a blank line.


This is what I have so far

*Edited Jpg*

I've never used VB/written macros.

I have the code to pull all information into 1 cell and seperate them with a comma.

I'm also ok with counting the string length/number of rows.

However the end goal is to have a macro which should work like this.

-User opens up excel file (the one not formatted correctly)

-User goes to Tools->Macros and runs the macro.

-The macro styles all the information on the sheet the way it's required and then saves it as a new file.


I know this is a bit of a broad request but I appreciate any help you could offer.
I have tried for the last 2 days to do it on my own but now I feel my lack of knowledge is just wasting time.

Many thanks again in advance.


process sounds fairly straightforward - IF the file is text, is it fixed length?  If so, no need to reformat as csv - I'd suggest almost that the macro asks for the file to be reformatted,then reads that (probably as text) outputting the header row first, then storing the information read from the file into rows 2 onwards.  If it finds a value of NULL it should instead store CIF.

is it possible to see a sample of the file (email is
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Aidan Heritage


I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!


My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2017 All rights reserved.