Uniform format for badly entered figures

John_356

John_356

New Member
Joined
Jan 17, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
Good day,

I'm dealing with a big database with lots of figures entered manually over the years by multiple different people. These people did not know any better than not to respect the previous one's format so it adds up to a huge database with cells with actual points and commas as well as formatted cells and currencies written with actual letters.

Any idea on how to deal with this? I'm thinking of a find/replace logic but can't figure it out so far.

Cheers to whomever gets me out of this one!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
PS: I need to be able to incorporate the solution in VBA so that formatting is done automatically for future similar issues. Thanks
 
Upvote 0
I think to have any chance, we would need to see some sample data that is a good representation of the sort of variety you might have (say 15-20 rows) and the expected results.
We would also need to know what characters appear because of formatting and what is actual text in the cells.
Best would be to give us the sample data and expected results with XL2BB so that we copy easily and also be sure we have exactly the same as you. Then you can give written explanation about the formatting v actual text with reference to specific cells in the XL2BB mini-sheet.
 
Upvote 0
Hi @Peter_SSs ,

Thanks for your reply - the ongoing issue is with dates. They're all written in dd mm yyyy format but some are with / some others with . and spaces, but most importantly some are texts and others dates, which makes it impossible to sort the data. Don't know how to change the format, any time I find a good code that works I find some dates have reversed day and month.

I'm looking for a VBA code to have it all in one single format.

Addin successfully added, but XL2BB's minisheet button doesn't unlock in Excel.

Below copypasted column:

DATE
01.12.2020
01.12.2020
01.12.2020
01.12.2020
01.12.2020
01.12.2020
01.12.2020
01.12.2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
01/12/2020
02.12.2020
02.12.2020
02.12.2020
02.12.2020
02.12.2020
02/12/2020
02/12/2020
02/12/2020
02/12/2020
02/12/2020
03.12.2020
03.12.2020
03.12.2020
03.12.2020
03/12/2020
03/12/2020
03/12/2020
03/12/2020
05.01.2021
05/01/2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06.01.2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
06/01/2021
07.12.2020
07.12.2020
07.12.2020
07.12.2020
07.12.2020
07.12.2020
07/12/2020
07/12/2020
07/12/2020
07/12/2020
07/12/2020
07/12/2020
08.12.2020
08.12.2020
08/12/2020
08/12/2020
09.12.2020
09.12.2020
09.12.2020
09.12.2020
09.12.2020
09.12.2020
09.12.2020
09.12.2020
09.12.2020
09.12.2020
09/12/2020
09/12/2020
09/12/2020
09/12/2020
09/12/2020
09/12/2020
09/12/2020
09/12/2020
09/12/2020
09/12/2020
11.01.2021
11.01.2021
11/01/2021
11/01/2021
12.01.2021
12.01.2021
12.01.2021
12.01.2021
12.01.2021
12.01.2021
12/01/2021
12/01/2021
12/01/2021
12/01/2021
12/01/2021
12/01/2021
14.01.2021
14.01.2021
14.01.2021
14.12.2020
14.12.2020
14.12.2020
14.12.2020
14.12.2020
14.12.2020
14/01/2021
14/01/2021
14/01/2021
14/12/2020
14/12/2020
14/12/2020
14/12/2020
14/12/2020
14/12/2020
15.12.2020
15/12/2020
16.12.2020
16.12.2020
16.12.2020
16.12.2020
16.12.2020
16.12.2020
16.12.2020
16.12.2020
16/12/2020
16/12/2020
16/12/2020
16/12/2020
16/12/2020
16/12/2020
16/12/2020
16/12/2020
19/01/2021
19/01/2021
19/01/2021
19/01/2021
19/01/2021
19/01/2021
19/01/2021
19/01/2021
21.12.2020
21.12.2020
21.12.2020
21/12/2020
21/12/2020
21/12/2020
22.12.2020
22.12.2020
22/12/2020
22/12/2020
24.12.2020
24/12/2020
28.12.2020
28.12.2020
28.12.2020
28/12/2020
28/12/2020
28/12/2020
29.12.2020
29/12/2020
30.12.2020
30/12/2020

Cheers
 
Upvote 0
John_356 said:
XL2BB's minisheet button doesn't unlock in Excel.
Click to expand...
What do you mean by "doesn't unlock"?


John_356 said:
I find some dates have reversed day and month.
Click to expand...
I'm not sure how you will ever deal with that. After all if "01/02/2021" is entered, how would you know if it was supposed to be 1-February or 2-January?

Anyway, does this do anything for you?
At the moment, with data in columns A,, I have it writing results in column B. Of course the results could just as well be put back in the original column if required.

VBA Code: 
Sub Fix_Dates()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = "\D"
    For i = 1 To UBound(a)
      a(i, 1) = DateValue(Format(RX.Replace(a(i, 1), ""), "00-00-0000"))
    Next i
    .Offset(, 1).NumberFormat = "dd/mm/yyyy"
    .Offset(, 1).Value = a
  End With
End Sub

Here is (some of) your original data (best in future if you don't give so much identical repeated data to keep the sample smaller) with code results in column B.

John_356.xlsm
AB
1DATE
201.12.202001/12/2020
301.12.202001/12/2020
401/12/202001/12/2020
501/12/202001/12/2020
603/12/202003/12/2020
703/12/202003/12/2020
805.01.202105/01/2021
905/01/202105/01/2021
1006.01.202106/01/2021
1106.01.202106/01/2021
1206/01/202106/01/2021
1306/01/202106/01/2021
1406/01/202106/01/2021
1506/01/202106/01/2021
1607.12.202007/12/2020
1707.12.202007/12/2020
1807/12/202007/12/2020
1907/12/202007/12/2020
2007/12/202007/12/2020
2108.12.202008/12/2020
2208.12.202008/12/2020
2308/12/202008/12/2020
2408/12/202008/12/2020
2509.12.202009/12/2020
2609.12.202009/12/2020
2709.12.202009/12/2020
2809/12/202009/12/2020
2909/12/202009/12/2020
3009/12/202009/12/2020
3109/12/202009/12/2020
3211.01.202111/01/2021
3311.01.202111/01/2021
3411/01/202111/01/2021
3511/01/202111/01/2021
3612.01.202112/01/2021
3712.01.202112/01/2021
3812/01/202112/01/2021
3912/01/202112/01/2021
4012/01/202112/01/2021
4114.01.202114/01/2021
4214.01.202114/01/2021
4314.12.202014/12/2020
4414.12.202014/12/2020
4514/01/202114/01/2021
4614/01/202114/01/2021
4714/01/202114/01/2021
4814/12/202014/12/2020
4915.12.202015/12/2020
5015/12/202015/12/2020
5116.12.202016/12/2020
5216.12.202016/12/2020
5316/12/202016/12/2020
5419/01/202119/01/2021
5519/01/202119/01/2021
5621.12.202021/12/2020
5721.12.202021/12/2020
5821.12.202021/12/2020
5921/12/202021/12/2020
6021/12/202021/12/2020
6121/12/202021/12/2020
6222.12.202022/12/2020
6322.12.202022/12/2020
6422/12/202022/12/2020
6522/12/202022/12/2020
6624.12.202024/12/2020
6724/12/202024/12/2020
6828.12.202028/12/2020
6928/12/202028/12/2020
7028/12/202028/12/2020
7128/12/202028/12/2020
7229.12.202029/12/2020
7329/12/202029/12/2020
7430.12.202030/12/2020
7530/12/202030/12/2020
Sheet1
 
Upvote 0
My solution would be to use Powerquery: 'New Query' on the 'Get & Transform' tab of the 'Data Ribbon', select 'From File' and then when its in the PowerQuery editor Transform the data type to DATE. It worked fine on the data you've provided, but check for 'Error's to find exceptions and you can manage them as necessary. IF the source is ever updated you only need to Refresh your data to get the latest information.

HTH
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

W
  • Solved
VBA Apply Formatting Based on 2 Criteria and If Another Cell Contains...
2
Replies
13
Views
261
Joe4
Joe4
C
  • Question
Date format in Switzerland...
Replies
7
Views
335
Joe4
Joe4
zookeepertx
  • Solved
Weird Excel quirks driving me crazy! Can anyone explain?
Replies
7
Views
180
zookeepertx
zookeepertx
A
  • Solved
Using IF STATEMENTS, FORMATS and VALIDATE to track tasks. HOW?
2
Replies
12
Views
826
abuchanan
A
SemperFi
  • Solved
VBA Form TextBox needs formatting
Replies
1
Views
339
SemperFi
SemperFi

Forum statistics

Threads
1,215,064
Messages
6,122,936
Members
449,094
Latest member
teemeren

Share this page

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