String manipulation using VBA

raora02 · #1 (**permalink**) 03-24-09, 15:19

Hi,

I have a spreadsheet using which I create a text file (using a macro). The spreadsheet has two columns with dates as strings in the format yyyymmdd. When I write to the text file, I want the dates to be written in the mmddyyyy format. What is the easiest way of doing this?

Thanks.

VBAExpert · #2 (**permalink**) 03-25-09, 19:33

hi

upload your sample file please..

Reagards

savbill · #3 (**permalink**) 04-02-09, 21:12

You can use string functions to extract parts of the date then recombine them in the order you require. Since you are already using a VB Script Macro it would be practical to modify this to process the date strings.

Code:

' Use trim to remove leading and trailing spaces
strDate = Trim(Cells(i, 2))
strYr = Left(strDate, 4)
strMD = Right(strDate, 4)
strDate = strMD & strYr

You didn't show any slashes in your date but if there were it would make this task even easier, and more exacting. Here's a way of parsing the date with slash delimiters

Code:

' start with the string in a variable
' having date format of "yyyy/mm/dd"
strDate = Trim(Cells(i, 2))
arryDt = Split(strDate, "/")
strDate = arryDate(1)
strDate = strDate & "/" & arryDate(2)
strDate = strDate & "/" & arryDate(0)
' now the date will be in the variable with
' format of "mm/dd/yyyy"

Internet Services

Web Development

Digital Marketing

Consumer Tech