Q re: TextToColumns

[Simon Woods]

Hi

I'm converting text to date cells using a combination of NumberFormat and
TextToColumn calls.

However, certain values are not being converted and are remaining, I think,
as text

For example

' ---- format each date column as date
For l_nCol = 1 To .Cols
If .IsDate(l_nCol) Then

' .Format contains formats e.g. mm/yy, dd-mmm-yyyy for
each column

l_oSheet.Columns(l_nCol).NumberFormat = .Format(l_nCol)
l_oSheet.Columns(l_nCol).TextToColumns
FieldInfo:=Array(1, 4)

End If
Next l_nCol

06/10/1998 gets correctly formatted to 06/Oct/1998
but
27/03/1998 doesn't get formatted and remains as it is as text (it is left
justified, whereas converted date values are right justified)

Can someone please explain where I'm going wrong ...

Many thanks

Simon

 

[DNF Karran]

Does sound like the value is getting stuck as text- possibly cos th
system is trying to read a UK format date (27/03/04) as a US Date (3r
of the 27th Month 2004). THis means vba fails to recognise the strin
as a date.

Maybe worth tring to set up the dates when running the text to column
command- and ensuring there are no " " round the date forcing it to b
text in the source data.

Dunca