Separating parts of a string in VBA

[Andrew]

Hello,
I have a string in cells(1,1). The value of the cell is "DATE" 1/1/10
THRU 3/1/10" I want to separate this string into the two new strings
such that one string would read "1/10/10" and the other would read
"3/1/10". In C programming, this would be fairly easy. But in VBA I
don't know any string functions which can read and compare individual
characters. Does anyone know how to accomplish what I am trying to
do?

thanks

 

[Rick Rothstein]

IF the DATE and THRU parts of your example text (along with their adjacent
blank spaces) appear in all the text strings that you want to separate, then
I would probably do it this way...

YourText = "DATE 1/1/10 THRU 3/1/10"
Begin = Split(YourText)(1)
Done = split(YourText)(3)

 

[ozgrid.com]

In B1 (format as date) and copied down;
=--TRIM(MID(A1,FIND(" ",A1),8))
In C1 (format as date) and copied down;
=--TRIM(MID(A1,FIND("THRU",A1)+5,256))

In VBA replace FIND for Instr function.

I would use Edit>Replace and replace "DATE" with nothing and "THRU" with
nothing then use Text to columns to split the 2 dates.

 

[Andrew]

In B1 (format as date) and copied down;
=--TRIM(MID(A1,FIND(" ",A1),8))
In C1 (format as date) and copied down;
=--TRIM(MID(A1,FIND("THRU",A1)+5,256))

In VBA replace FIND for Instr function.

I would use Edit>Replace and replace "DATE" with nothing and "THRU" with
nothing then use Text to columns to split the 2 dates.

Thanks. Both of these work fine.

 

[Rick Rothstein]

Actually, there is a problem with ozgrid's first formula... it will return
the wrong date (year part) if the date is in November or December after the
9th of the either of those months. Since the text part of your cell values
are fixed and constant, and since the year part of your dates are always
2-digits long, you can use these formulas to retrieve the dates....

=--MID(A1,6, FIND(" ",A1,7)-6)

=--MID(A1,FIND("THRU",A1)+5,8)

By the way, your Subject line said you were after a VBA solution, so I did
not think to give you a formula solution originally.