Left Function in VB

[Vampy99]

Hi,

In column A I have a number of times in number format e.g 47715.XXXXX

I only want to select the first 5 characters so I can then convert this to a date without having the time (it messes up my pivot table!)

I then want these to be pasted in column B.

Does anyone know the syntax for this in VB?

I'm still new to this language & am trying to learn as much as I can!

Thank you for any help in advance.

Luke

 

[VoG]

You can do that with a formula

Excel Workbook
	A	B
1	47715.12345	47715
Sheet2

 

[Vampy99]

You can do that with a formula

Excel Workbook
	A	B
1	47715.12345	47715
Sheet2

Thanks for the response VoG. I would usually use =LEFT(A1,5) but I've read that there is a VB function for this so wanted to give that a try? I've just been unable to find the syntax that gives me what I want

Thanks,

Luke

 

[RoryA]

The VB function is also Left (or Int would be better here, as Peter suggested).

 

[Vampy99]

Ok, so is this the best way to do it? Doing the left formula & copying the formula to the bottom row?

Range("B2").Formula = "=INT(A2)"
Range("B2").Copy
Range("B3:B" & NumRows & "").PasteSpecial

"NumRows" is a variable which lets me know the last number of the last row.

 

[VoG]

You can do this

Range("B2:B" & NumRows).Formula = "=INT(A2)"

 

[VoG]

Or if you don't want to keep the formulas

With Range("B2:B" & NumRows)
    .Formula = "=INT(A2)"
    .Value = .Value
End With

 

[Vampy99]

Or if you don't want to keep the formulas

With Range("B2:B" & NumRows)
    .Formula = "=INT(A2)"
    .Value = .Value
End With

Great, thank you for the help guys