Cleaning Data

Hi,
I'm looking to Clean some Data to ultimately be used for Pivot Table and Charts.

The data I pull can be downloaded in .xlsx or .csv formats. The issue is cells containing dates or numbers show a date or numbers but in the formula bar have something similar to:
="01/05/2015" or ="0450"
I want to remove the extra stuff leaving only the dates or numbers.

The attached files are identical just the different formats.

Also in the case of the number sets they will always have four digits. How do I format those cells to reflect the leading zeros like - 0450 vice 450 in the cell.

Thank you in advance,

Jim

JB,

Thank you for that. I had done that before but had forgotten about it.

Jim

FlameRetired,

I had tried that but not all 4 digit numbers lead have a Zero. Some of them have a number in the first position so that option doesn't work in this case.

Jim

Hi sorensjp,

PFA and PFB for the small macro. Paste this code in new module in Visual Basic Editor. Click on the sheet which contains the raw data and run this macro by pressing shortcut key ALT + F8. I hope this code will help.

Sub FormulaRemoving()

'Declaring variables
Dim DynamicRange As Range
Dim CellValue As Variant

'Defining the range
Set DynamicRange = Range("A1", Range("A1").SpecialCells(xlCellTypeLastCell))

'Looping through all the cells in the range
For Each CellValue In DynamicRange
    
    'Checking whether value in the cell is of date data type
    If IsDate(CellValue) Then
        
        'Formatting for date data type
        CellValue.NumberFormat = "m/d/yyyy"
    Else
        'Formatting for number data type
        CellValue.NumberFormat = "0"
    End If
    
    'Removing formula
    CellValue.Value = CellValue.Value
Next

'Auto adjusting the size of the columns
Columns.AutoFit

End Sub

Regards
Ramandeep Singh
Please Click * icon to Add Reputation if my answer helped!

Raman1667,

This actually works nicely for me. I'm messing around with some my data to possibly create a dashboard. I don't have any idea what my end product might be that is if I get that far.

Excel is so powerful and I'm still learning. Thank you for your help.

Jim

Originally Posted by sorensjp

Raman1667,

This actually works nicely for me. I'm messing around with some my data to possibly create a dashboard. I don't have any idea what my end product might be that is if I get that far.

Excel is so powerful and I'm still learning. Thank you for your help.

Jim

Thanks Jim,

IF this problem is resolved, Kindly mark this question as solved.

Thanks & Regards
Ramandeep Singh
Please Click * icon to Add Reputation if my answer helped!

Raman1667,

This actually works nicely for me. I'm messing around with some my data to possibly create a dashboard. I don't have any idea what my end product might be that is if I get that far.

Excel is so powerful and I'm still learning. Thank you for your help.

Jim

Originally Posted by sorensjp

Raman1667,

This actually works nicely for me. I'm messing around with some my data to possibly create a dashboard. I don't have any idea what my end product might be that is if I get that far.

Excel is so powerful and I'm still learning. Thank you for your help.

Jim

Hi Jim,

If my answer helped, Kindly add reputation by clicking on the small "star" icon located in the lower left corner of the post and mark this thread as SOLVED.

Thanks & Regards
Ramandeep Singh
Please Click * icon to Add Reputation if my answer helped!