1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search Columns which have data “##.##.####” & convert all “.” To “/”

Discussion in 'VBA Macros' started by Chirag R Raval, Aug 9, 2017.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    Many files I received daily with many rows…they all are generated from “SAP” to excel…

    For Date columns ..Excel need “/” as a separator between –date/month/year to properly sort Date instead of “.”


    Can anyone help me for code to Search Columns which have data “##.##.####” & convert all “.” To “/” ?


    Regards,

    Chirag Raval
  2. AliGW

    AliGW Member

    Messages:
    68
    Are the dates presenting as real dates or text? If text, then select all the relevant columns and use find and replace:

    Find: .

    Replace: /

    Record a macro as you are doing it if it's going to be a repetitive task.
    Chirag R Raval likes this.
  3. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear AliGW,

    Many thanks for your response...& take interest to resolve..

    But there are more then 1 columns have date for example… “17.03.2017 “ found on the sheets & when forgot to convert “.” To “/, on any columns… & after print the report there are found 1 or 2 columns not sorted on date properly…so ? Re-generate whole report from start ….& re-print…that’s means waste of time …effort….& resources……though in cell formatting done “.” as date separator ..its compare with system date time

    & its also effect on sorting …in short ..


    If VBA code found to search for columns have data type “##.##.####” & its convert in all “.” In “/” so excel recognize that as date…& what after that? perfect sort, perfect print of reports…without forget anything date related..


    Hope you can understand...


    Regards,

    Chirag Raval
  4. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear All,

    Hope your co-operations..

    Regards,
    Chirag Raval
  5. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    If code just search in 1st , 2nd or 3rd row, if found first instance of "##.##.####", whole column's data's all "." convert in "##/##/####", then proceed for next column next search...may be this can be fast..

    Regards,
    Chirag Raval
  6. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,787
    Something like below shall get you started:
    Code (vb):
    Public Sub ReplaceDotWithSlash()
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange
        If rng.Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" Then
            rng.Value = Replace(rng.Value, ".", "/")
        End If
    Next
    End Sub
    Chirag R Raval likes this.
  7. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Shrivallabha..

    Thanks for your effort ...& give a concept of [ ] ...but sorry to say.. after run ..result is...some is like text & some is like date..
    So can not be sort...may be whole column also reuire to forcefully format whole column..as we generally do force via text column & press finish without intentionaly to part of column...just force excel to recognise that column's data as dd/mm/yyyy. ...& this force normally do it"s job perfactly.... In your code....seems loop find in whole sheet ...& every cell..so take long time ..& also create .messup that's technically unmatch & less logical ...

    For eficiancy..& fast work...There are need to search 1st instance in columns..in other words..just in One row...if found 1st instance...quickly
    Convert that cell's whole column's "." In "/". & also force to whole column as " did/mm/yyyy" then proceed for next search in only that row in next .column....for "##/##/####"....& convert......&force....in this case may be file pre formated as text...& that factor also effact as bad sort result....so may be need first to force as dd/mm/yyyy & then replace all "." To "/"......but that column only....&after process search in next column in same row...

    Hope I try my best to focus on the matter above ...
    & also hope for good code from you..

    Again thanks for your effort..

    Regards,

    Chirag Raval
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    It is not so straightforward ; if Excel has to recognize any text as a valid date , it has to be so according to your system date format.

    If your system date format is dd/mm/yyyy , then a text string such as 17.03.2017 will be converted to a text string 17/03/2017 , and Excel will be able to recognize this as a valid date , and will interpret it as March 17 , 2017.

    If a text string such as 10.11.2017 is supposed to represent 11th October , 2017 , the conversion to 10/11/2017 will be done , and Excel will interpret it as 10th November , 2017 , which is not what you want. It will be difficult to identify such mistakes.

    A text string such as 03.29.2017 , which may represent March 29 , 2017 will be converted to 03/29/2017 , but Excel will not recognize this as a valid date , since it is not so according to the system date format. Thus , even after conversion , this will remain a text string.

    Please upload a workbook with enough data in it.

    Narayan
    Chirag R Raval likes this.
  9. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,787
    As Narayan has explained and Code was to give you an idea.
  10. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    My System Also Dear Shrivallabha sir, & Narayn sir...

    Many thanks for you both experts...for reply..& thrown focus on twisting in date types...

    I attached my sample excel file ..about 5000 rows in it..
    (original daily about 60,000 rows)...please refer yellow highlighted columns
    for date...

    I am from Indians mostly use British style date format "DD/MM/YYYY"
    my pc have also system type "dd/mm/yyyy"....& file also generated FROM SAP with same structure "DD/MM/YYYY"

    Hope your little help..

    Regards,

    Chirag Raval

    Attached Files:

  11. AliGW

    AliGW Member

    Messages:
    68
    Done in under five seconds with find and replace. :)

    Attached Files:

    Chirag R Raval likes this.
  12. AliGW

    AliGW Member

    Messages:
    68
    I should add that I simply CTRL selected the yellow columns before doing the find and replace.
  13. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    Try this :
    Code (vb):

    Public Sub ReplaceDotWithSlash()
               Dim rng As Range
               
               For Each rng In ActiveSheet.UsedRange
                   If rng.Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" Then rng.Value = CDate(Replace(rng.Value, ".", "/"))
               Next
    End Sub
     
    Narayan
  14. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,787
    For posted sample following will work:
    Code (vb):

    Public Sub ReplaceDotWithSlash()
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange
      If rng.Value Like "[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]" Then
      rng.Value = DateValue(Mid(rng.Value, 4, 2) & "/" & Mid(rng.Value, 1, 2) & "/" & Mid(rng.Value, 7, 4))
      End If
    Next
    End Sub
     
  15. AliGW

    AliGW Member

    Messages:
    68
    And here's a cheeky little macro I recorded whilst doing the find and replace:

    Code (vb):
    Sub Macro_Dots_to_Dashes()
    '
    ' Macro_Dots_to_Dashes Macro
    '

    '
       Range("G15,B:B,L:L").Select
        Range("L1").Activate
        Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End Sub
     
    Tim Hanson and Chirag R Raval like this.
  16. p45cal

    p45cal Well-Known Member

    Messages:
    923
    You can also use Text to Columns:
    upload_2017-8-10_11-5-8.png
  17. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear AliGW & P45cAL,

    Thanks you both for effort for me...
    AliGW’s recorded macro run ..but result same as per shrivallabha…
    (Some Text---Some Date- Can not sort-)

    also thanks to p45cal...but till now,..I already done it manually...just select column & find & replace "." to "/"...& it's okay...but...I mentioned my 1st post
    if there are chance to forget even manually that....whole work should be done from start..that's the problem...

    if efficient code found...it can be quickly convert & also force to whole column "dd/mm?yyyy"..

    hope all can understand...

    Regards,
    Chirag Raval
  18. AliGW

    AliGW Member

    Messages:
    68
    I see no problem sorting your file by either date column after find and replace. Find and replace will automatically force the output to date format. I really do fail to see what is going wrong for you - this is so simple.

    upload_2017-8-10_11-48-59.png
    Chirag R Raval likes this.
  19. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear AliGW,

    Thanks...for your efforts...

    firstly your recorded macro run on particular predefined...fixed columns which we mentioned in code but situation is ..may be it can differ from file to file .......so code need to search for that formatting...only & decide to covert & format whole column with force...(with if condition in code..(if found this then do this)

    secondly...your excel looks 2016....but I use excel 2010 ... may be its effect on this... but really ...after run your code...in date column...some is text& some is like date...also when try to sort
    there are message box appear that "Some Number formatted as text" and 2 options " sort anything as text & sort number as number "...appear...

    in short there should be I must have code to force excel to columns data
    convert in "dd/mm/yyyy" format...

    whole thing is found that file generated from SAP..is pre-formatted with text...
    & we must force to excel that convert it to either number or dd/mm/yyyy..

    hope for code available

    regards,
    Chirag Raval
  20. p45cal

    p45cal Well-Known Member

    Messages:
    923
    This is NOT the same! It is very likely that doing it that way, Excel will interpret wrongly some dates depending on your locale. See Narayan's msg#8 in this thread.
    To run this short macro, you must FIRST SELECT the cells (a range no wider than one column) you want it to work on, then run it. It tells Excel how to interpret the dates (DMY) and converts them to dates in some local format according to your locale. It is up to you to format those dates differently if you want to.
    Code (vb):
    Sub blah()
    Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 4)
    End Sub
    Last edited: Aug 10, 2017
    Chirag R Raval likes this.
  21. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,914
    All of solution provided should work. However, I suspect that your system date is actually set to M/dd/yyyy or other variants with month at start. Hence, why some dates are treated as text, as indicated by Narayan.

    shrivallabha's code in post #14 should work regardless of system date format (you just have to apply formatting later if dd/mm/yyyy is desired).

    Another sample. Slightly different logic used (though it's probably safer to use pattern check).
    Code (vb):
    Sub Demo()
    Dim myArr
    Dim colIndex As String
    myArr = Sheet1.UsedRange.Value

    For i = 1 To UBound(myArr)
        For j = 1 To UBound(myArr, 2)
            x = Split(myArr(i, j), ".")
            If UBound(x) = 2 Then
                If Len(x(2)) = 4 Then
                    If InStr(1, colIndex, j) = 0 Then colIndex = IIf(Len(colIndex) = 0, j, colIndex & "," & j)
                    myArr(i, j) = DateSerial(x(2), x(1), x(0))
                End If
            End If
        Next
    Next

    x = Split(colIndex, ",")
    With Sheet1
        .UsedRange = myArr
        For i = 0 To UBound(x)
            .Columns(CLng(x(i))).NumberFormat = "dd/mm/yyyy"
        Next
    End With
    End Sub
  22. p45cal

    p45cal Well-Known Member

    Messages:
    923
    If you want to try and process the currently active sheet in one go then you could try this development:
    Code (vb):
    Sub blah2()
    Set URng = ActiveSheet.UsedRange
    For Each cll In Intersect(URng, Rows(1))
      If InStr(1, cll.Value, "date", vbTextCompare) > 0 Then Intersect(cll.EntireColumn, URng).TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 4)
    Next cll
    End Sub
    It looks for the word 'date' in the top row and works on those columns only.
  23. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear p45cal,

    I test this code after select whole single column ...

    Really amazing...how its magically ...convert as required...?
    can you explain how can its bypass matter of "." to "/"?

    can you add some Functionality in your code for auto search that columns?
    & run your code on that columns?

    really magical code given by you......appreciated..

    hope some adding of search functionality..

    Regards,
    Chirag Raval
  24. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sirs,

    (1) Mr p45cal (2) Mr Chihiro....

    many thanks...you booths....

    (1) p45cal's 1st code run but require some functionality

    (2) 2nd code "heading should contain date" pending for check...
    (but I hope....something technique also available there to just auto find
    "##/##/####" structure)

    (3) Mr Chihiro's code require variables ...for which...

    I put I &j as integer but...on ...."X = Split(myArr(i, j), ".")"
    I stop there ...which type of variable it should be?

    I already declare it as variant...but "type mismatch " displayed..

    seems this thread meets its final solutions soon...

    Regards,
    Chirag Raval
  25. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,914
    Hmm? If you have "Option Explicit"... then declare it as "Long".

    FYI - In newer versions of Excel, INT is actually converted to LNG in the background (I think Excel 2007/2010 or later). So, there's no point in using Integer, and using Long actually saves time (albeit very small amount).
    Tim Hanson and Chirag R Raval like this.

Share This Page