• 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...

  • 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 “/”

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
 
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.
 
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
 
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
 
Something like below shall get you started:
Code:
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
 
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
 
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
 
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
As Narayan has explained and Code was to give you an idea.
 
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
 

Attachments

  • FOR CHANDOO.COM-ORDER STATUS.xlsx
    589.9 KB · Views: 9
I should add that I simply CTRL selected the yellow columns before doing the find and replace.
 
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
For posted sample following will work:
Code:
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
 
And here's a cheeky little macro I recorded whilst doing the find and replace:

Code:
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
 
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
 
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
 
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
 
...but till now,..I already done it manually...just select column & find & replace "." to "/"...& it's okay...but...I mentioned my 1st post
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.
if efficient code found...it can be quickly convert & also force to whole column "dd/mm?yyyy"..
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:
Sub blah()
Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 4)
End Sub
 
Last edited:
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:
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
 
If you want to try and process the currently active sheet in one go then you could try this development:
Code:
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.
 
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
 
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
 
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).
 
Back
Top