I have this string that a customer is sending us in a report. It looks like
the following.

COMPANY NAME; NAME; 11/22/04

I wanted to use the Text to Column feature, but every cell varies in length
and amount of data. I am curious if there is a way to search the cell and
strip out the date out of each cell and move them into a different cell on
the same row. Or can I setup a macro to copy the entire column, and then
strip out everything but the date. Any information that could be provided
would be greatly appreciated.

Thanks,

It depends on the date and your cell is structured. You may be able to use
VBA's Left, Right, or Mid functions to extract the substring that contains
the date and then place that substring in another cell.

"Mikey" wrote:

I have this string that a customer is sending us in a report. It looks like
the following.

COMPANY NAME; NAME; 11/22/04

I wanted to use the Text to Column feature, but every cell varies in length
and amount of data. I am curious if there is a way to search the cell and
strip out the date out of each cell and move them into a different cell on
the same row. Or can I setup a macro to copy the entire column, and then
strip out everything but the date. Any information that could be provided
would be greatly appreciated.

Thanks,

Hi Mikey,

With the data in A1, try these formulas in two other cells where you want
the info to be.

Use this to extract the date:
=RIGHT(A1,8)

Use this to extract the Co. Name and Name, minus the last ;
=LEFT(A1,LEN(A1)-(LEN(RIGHT(A1,10))))

Then Copy Edit Paste Special Values OK to get rid of the formulas
and retain the new data.

HTH
Regards,
Howard

"Mikey" wrote in message
...
I have this string that a customer is sending us in a report. It looks
like
the following.

COMPANY NAME; NAME; 11/22/04

I wanted to use the Text to Column feature, but every cell varies in
length
and amount of data. I am curious if there is a way to search the cell and
strip out the date out of each cell and move them into a different cell on
the same row. Or can I setup a macro to copy the entire column, and then
strip out everything but the date. Any information that could be provided
would be greatly appreciated.

Thanks,

Hi Mikey,

The Text to Column feature does the job nicely. Make sure you select
Delimited on the wizard's first page, and Semicolon on the second
page.

Regards,
Vic Eldridge





"Mikey" wrote in message ...
I have this string that a customer is sending us in a report. It looks like
the following.

COMPANY NAME; NAME; 11/22/04

I wanted to use the Text to Column feature, but every cell varies in length
and amount of data. I am curious if there is a way to search the cell and
strip out the date out of each cell and move them into a different cell on
the same row. Or can I setup a macro to copy the entire column, and then
strip out everything but the date. Any information that could be provided
would be greatly appreciated.

Thanks,