Caron has attended:
No courses
Cleaning up raw data into a readable format
Hi,
I attend an excellent Excel Formula course with Jan on 6th November. I mentioned I download raw data each month from Amex on the monthly employee travel costs for EMEA.
It has been difficult and time consuming to work with and drive reports from as it uses two different reports (one for hotel and other for flights). Both are in different data and layout format.
So I have manually clean and put the data into a consistent format using various tabs and formula.
Jan said he would kindly write the VBA code for me to automatically clean and format the raw data into a usable format.
Is Jan still able to do this if I send over further details?
Many thanks
Caron Willard
RE: Cleaning up raw data into a readable format
Hi Caron,
Thank you for the forum question.
Yes, as I said on the course when we talked about the clean and trim functions, I would never add columns to remove unprintable characters or unwanted spaces. I will use a VBA code:
The code I am using is the code below. It will not add formatting or change the structure of the data only clean the data from strange characters, double spaces, space before and after the cell content.
Sub TrimCleanData()
Dim rng As Range
Dim Area As Range
Set rng = Cells.SpecialCells(xlCellTypeConstants)
For Each Area In rng.Areas
Area.Value = Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))")
Next Area
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
