How to convert exported text date to desired date format with excel vba.

Originally Posted by Norie

This will do the conversion without any helper columns.

Dim rng As Range

    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))

    With rng
        .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
                       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                       Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
                       FieldInfo:=Array(1, 5), TrailingMinusNumbers:=True
        .EntireColumn.AutoFit
    End With

    With rng.Offset(, 1)
        .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
                       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                       Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
                       FieldInfo:=Array(1, 5), TrailingMinusNumbers:=True
        .EntireColumn.AutoFit
    End With

Thanks Norie for helping me. Would you please explain a bit what will this piece of code do? Because I am not expert of VBA. But I tried to include your code within my code (which is a sub-routine known as pastefromclipboard()) and my code inserts the data at desired location, but when it comes to your code then, it gives an error '1004' (No data was selected to parse.)

Would you please guide me that how to use this code.

Please find the sheet attached with my code for pasting the data from clipboard. This code runs well very first time, but when I run it second time the date columns show only ########, which is strange to me.

You can copy the below data and run the macro pastefromclipboard() to see what it does.

SBO507175300,20130705,20130920,62
SBO864070985,20130705,20130919,62
SBO177492601,20130705,20130927,77
SBO710237444,20130705,20130920,62
SBO962342877,20130705,20130930,59
SBO19704115,20130705,20130918,81
SBO191859391,20130705,20130927,67
SBO477581883,20130705,20130929,72
SBO337574486,20130708,20130929,82
SBO58479395,20130708,20130924,78
SBO462419969,20130712,20130912,114
SBO555497224,20130712,20130929,69
SBO628084891,20130712,20130924,74
SBO569899162,20130719,20130923,82
SBO350825729,20130719,20130926,62
SBO171753411,20130719,20130927,49
SBO613615026,20130719,20130930,58
SBO925727239,20130719,20130925,53
SBO366112445,20130719,20130922,67
SBO123327454,20130719,20130924,54
SBO251091491,20130719,20130927,84
SBO458342174,20130719,20130927,68

Regards