Date format Pasting from report not playing Nice

Dannygreen1992

New Member
Joined
Aug 5, 2018
Messages
5
Hi All

I'm having a nightmare with a workbook, it has a whole bunch of amateur code, but it worked

however! the report this workbook imported its results from has been changed and the Date formats are acting crazy, swapping the months and days. The issue being that the workbook uses an Index Match formula and can no longer match the dates, it appears the new format adds a null value to each cell

The only thing that has worked is copying the cells into notepad and pasting back into the table, which I'm unsure how to write a module for as I've never activated or worked with notepad in VBA

I need a method of targeting a whole sheet and removing all null values, this is how they appear in the raw report:

Date
07/04/2019
08/04/2019
09/04/2019
10/04/2019
11/04/2019
12/04/2019
13/04/2019

And this is the outcome once the workbook has imported the data and made it into a table, he bottom cell is the only one showing the actual date, but the null value still ruins the index match:

04/07/2019
04/08/2019
04/09/2019
04/10/2019
04/11/2019
04/12/2019
13/04/2019

Cell as Date {dd-mm-yyyy}:

43650
43681
43712
43742
43773
43803
13/04/2019

Cells as text:

04 July 2019
04 August 2019
04 September 2019
04 October 2019
04 November 2019
04 December 2019
13/04/2019

Cells as date

Any and all ideas appreciated, again I can confirm every cell has a null value at the end of the string

Many Thanks

Danny
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You might try Rick Rothstein's method to clean the spaces and non-printing characters.
http://www.excelfox.com/forum/showthread.php/155-Trim-all-Cells-in-a-Worksheet-VBA#post1092

Here's his function:
Code:
Function CleanTrim(ByVal S As String, OptionalConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean AsVariant
  CodesToClean = Array(0, 1, 2, 3, 4, 5,6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24,25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S =Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) ToUBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X)))Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim =WorksheetFunction.Trim(S)
End Function

Then you could use a sub like this to clean your worksheet (modify as needed).
Code:
Sub CleanAll()
Dim i&, j&
Dim rng As Range, Arr As Variant
 
Set rng = [A1].CurrentRegion
Arr = rng
 
For i = LBound(Arr, 1) To UBound(Arr, 1)
  For j = LBound(Arr, 2) To UBound(Arr, 2)
    Arr(i, j) = CleanTrim(Arr(i, j))
  Next j
Next i
rng = Arr
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top