Separating parts of a string in VBA
 

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

Separating parts of a string in VBA
 

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)

--
Rick (MVP - Excel)



"Andrew" wrote in message
...
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

Separating parts of a string in VBA
 

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 EditReplace and replace "DATE" with nothing and "THRU" with
nothing then use Text to columns to split the 2 dates.



--
Regards
Dave Hawley
www.ozgrid.com

"Andrew" wrote in message
...
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

Separating parts of a string in VBA
 

On Mar 31, 6:03*pm, "ozgrid.com" wrote:
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 EditReplace and replace "DATE" with nothing and "THRU" with
nothing then use Text to columns to split the 2 dates.

--
Regards
Dave Hawleywww.ozgrid.com

"Andrew" wrote in message

...

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

Thanks. Both of these work fine.

Separating parts of a string in VBA
 

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.

--
Rick (MVP - Excel)



"Andrew" wrote in message
...
On Mar 31, 6:03 pm, "ozgrid.com" wrote:
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 EditReplace and replace "DATE" with nothing and "THRU" with
nothing then use Text to columns to split the 2 dates.

--
Regards
Dave Hawleywww.ozgrid.com

"Andrew" wrote in message

...

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

Thanks. Both of these work fine.