CODEWITHSUNDEEP
excelvba
Smruti R Mohanty
Smruti R Mohanty

Reputation: 11

Excel Macro to validate CSV field format

I am writing a macro to read a CSV file where there is a field called systemdate available and the format of that field is "yyyy-mm-dd hh:mm:ss.0".

So while reading the CSV I need to check the field value to see if it is in the above mentioned format or not.

For example, if the field value is 2017-04-18 12:56:32.0 then it's correct. If not then it should throw an error.

Upvotes: 0

Views: 904

Answers (1)

OES
OES

Reputation: 321

use NumberFormat to determine it. If your range is in cell A1 to check it then use: You can change that range per your needs.

Sub check_Format()
Dim chk As String

chk = NUMFormat(Range("A1"))

If chk = "yyyy-mm-dd hh:mm:ss.0" Then
    MsgBox "Format OK"
Else
    MsgBox "Format Not OK"
End If
End Sub

Function NUMFormat(r As Range)
  NUMFormat = r.NumberFormat
End Function

Former function you can use to check format directly in the cell. For example in B1 write:

=NUMFormat(A1)

Upvotes: 1

Related Questions