When I import a txt file and convert into columns I have 1 column that is a text number (Ex. 20250222). What i need is a formula to convert that text into a date such that it becomes one of the following: 2025.02.22; or 02/22/2025. Maybe, this should be a VBA that executes on importing the date or with a macro button. Looking forward to some help.
Will the text number always be 8 digits like you show here? In that case I expect something like =DATE(LEFT(textnumber,4),MID(textnumber,5,2),RIGHT(textnumber,2)) will return the desired date serial number, which you can then format in whatever date format you desire using number formatting.
If you prefer VBA, VBA has similar text and date functions that can do the same thing.
Originally Posted by shg
As I need to convert all records in Column E to the date format, VBA that would do the conversion as the data in converted from text file to columns might be best way going forward. Each import is over 2,000 records. Help is greatly appreciated.
Why do you think VBA would be best? I can enter that formula into a cell and copy/paste/fill down a lot faster than I can write a VBA procedure for that.
As I said, VBA has the same basic functions. I would expect a similar VBA statement inside of a loop would work just fine.It's up to you how you want to proceed. So far, I have not seen anything in your description of the problem that would suggest to me that VBA is a better programming language for this task than simple worksheet functions.
Please Login or Register to view this content.
This shorter formula would also work the same way and handle the entire range all at once...
=0+TEXT(A1:A2000,"0000-00-00")
Note: Your formula will also handle the entire range all at once if you change each textnumber reference to the datarange reference.
No formulas
Text to Columns-->Delimeted-->Next-->Next-->Date--YMD--Finish
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks