CODEWITHSUNDEEP
vbaexcelnumber-formattingstring-parsing
Mehdi Flame
Mehdi Flame

Reputation: 63

Numberformat does not change cell value in vba excel

I have a range of numbers formatted as text in column A that represent date:

02 10 'which means 02 October 
03 11 'which means 03 November
04 12 'which means 04 December

What I'm trying to do is to parse this string in order to turn it into a proper date format here is my code:

Dim l As Long
Dim s As String
Dim d As Date
Columns("a").NumberFormat = "0000"
l = Range("A3").Value
s = CStr(l)
d = DateSerial(CInt(2017), CInt(Left(s, 2)), CInt(Right(s, 2)))
Range("A19").Value = d
Range("a19").NumberFormat = "mm/dd/yyyy"

The problem that I encounter is when a cell value in column A is 2 10 (second of October) my number-format does force it to become 0210 but the value of the cell itself is still 210 so my string parsing doesn't work how can I bypass this problem?

Upvotes: 2

Views: 1727

Answers (1)

user4039065
user4039065

Reputation:

As mentioned in comments, Split on the cell's .Text property.

Range("A19").Value = dateserial(2017, split(Range("A1").Text, chr(32))(1), split(Range("A1").Text, chr(32))(0))
Range("A19").NumberFormat = "mm/dd/yyyy"

Upvotes: 2

Related Questions