Delimit not working

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,490
I have 2 columns of data that hold dates. Some of the entries have a single quote before the data.

When I use the following script on each column
Code:
Range("E2").Activate
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="'", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
it will convert all the data in column D, but the quoted code above doesn't work on column E.

When I recorded the code it worked, but when executed in a macro it doesn't seem to recognise the single quote in the second column and doesn't convert to a date.

Does anyone know why this is happening?


Thanks
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

is this any better?

Code:
    Range("E2").Select
    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="'", FieldInfo:=Array(1, 4
)
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,490
Actually just managed to sort the problem as the notification of a reply came thru'.

The data is being copied from an Access database and I was copying over with paste special, text. If I copy as csv the data drops in in the formats I have set in the columns so no need to delimit.

Thanks for looking at it anyway.
 

Forum statistics

Threads
1,141,626
Messages
5,707,489
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top