Macro: Text to Columns

I have created a macro that selects everything in a sheet starting on A4 downwards. I want to use Text to Columns, delimited (comma and space) to separate the data.

When I do it manually, it does not change the date into a date format but keeps it as text (ex. 05/29/21). However, when I run the macro I created through the manual process, it changes it to a date format. I do not want it in the date format. I also do not want it to be in text format either

How do I edit the VBA to prevent it from converting anything to a date format without converting it to text, but just keeping it as general?

Cross-posted: https://www.mrexcel.com/board/thread...lumns.1172499/

Please Login or Register  to view this content.

I do not want it in the date format. I also do not want it to be in text format either

so what DO you want it as?

Originally Posted by HDTV

I want it formatted as general. When I upload manually, it is already set to general so 05/29/21 is exactly that...

If that is how it is displaying under General formatting, then that is already text, not a date. A real date...05/29/21...set as General would appear as 44345
also, we cant work with pics (yours didnt come through anyway), we need actual (clean) excel files to work with

Originally Posted by jbelliot

Sorry if I am highjacking your thread but I cannot seem to get my spreadsheet to load under normal question post and only way to see GOADVANCED button is on replies not on news message If any body can help my excel file attached showing problem many thanks and apologies if I am out of order still new to this forum regarding asking for help

Yes you are.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Originally Posted by HDTV

I think I found a workaround where I leave it as general and it auto-formats to date and time, which I can work with. What I need to do is figure out how to write a VBA code that formats date values (greater than 1) as dates and time values ( 0 > x > 1) as time.

Why not just format everything as date/time? Then it wont matter which is which