2

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?

Community
  • 1
  • 1
Mehdi Flame
  • 63
  • 8

1 Answers1

2

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"
  • Somehow i get a running error "9" Subscript out of range ;( – Mehdi Flame Feb 22 '18 at 23:46
  • @MehdiFlame then you do not have the space between the numbers like you are showing. – Scott Craner Feb 22 '18 at 23:49
  • Step 1 - type `'03 10` into A1. Step 2 - run these two lines of code. Step 3 - `10/03/2017` is in A19. –  Feb 22 '18 at 23:53
  • You are totally right since the data was extracted through OCR some do have space others not can i do something about it? – Mehdi Flame Feb 22 '18 at 23:53
  • @MehdiFlame when you ask a question it is expected that you would show all possible inputs and desired outputs, not just the ideal cases. It makes us answer the wrong question. – Scott Craner Feb 22 '18 at 23:57
  • 1
    maybe `Range("A19").Value = DateSerial(2017, Right(Range("A1").Text, 2), Left(Range("A1").Text, 2))` –  Feb 22 '18 at 23:59
  • It seems i cant choose @ScottCraner answer as the right one as it answered my original question anyhow ill choose jeeped answer as the right one as it is fairly similar to scott's solution and sorry guys for the inconvenience . – Mehdi Flame Feb 23 '18 at 00:07