Cell containgn a date and a text
 

Can someone please advise, how can I extract a date from a cell which
contains a date and a text.

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100

FEDERAL NATL MTG ASSN BNCHMRK 3

I would like to have one cell contain a date 11/11/07 if it is there else
"none callable is there's no date.

FEDERAL NATL MTG ASSN BNCHMRK 3

also, how can I have a cell display only company name and not the rest of
the text.

FEDERAL HOME LN BKS

FEDERAL NATL MTG ASSN

Thank you in advance for your help

Cell containgn a date and a text
 

Assuming that the date format is *always* the same: mm/dd/yy, *AND* there
are no other "/" within the string.

=IF(COUNT(FIND("/",A1)),--MID(A1,FIND("/",A1)-2,8),"none")

Format the cell as DATE

also, how can I have a cell display only company name and not the rest of
the text.

So what's the company name for this:

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100

Good luck with that!


--
Biff
Microsoft Excel MVP


"Iguss" wrote in message
...

Can someone please advise, how can I extract a date from a cell which
contains a date and a text.

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100

FEDERAL NATL MTG ASSN BNCHMRK 3

I would like to have one cell contain a date 11/11/07 if it is there else
"none callable is there's no date.

FEDERAL NATL MTG ASSN BNCHMRK 3

also, how can I have a cell display only company name and not the rest of
the text.

FEDERAL HOME LN BKS

FEDERAL NATL MTG ASSN

Thank you in advance for your help

Cell containgn a date and a text
 

Thank you so much.

Names are,

FEDERAL FARM CR BKS CONS CONS B Callable 05/01/08@100
Federal Farm Credit Bank

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100
Federal Home Loan Bank

FEDERAL HOME LN BKS CONS BD 6.6
Federal Home Loan Bank

FEDERAL NATL MTG ASSN BNCHMRK 3
Federal National Mortgage Association

Thank you again

"T. Valko" wrote:

Assuming that the date format is *always* the same: mm/dd/yy, *AND* there
are no other "/" within the string.

=IF(COUNT(FIND("/",A1)),--MID(A1,FIND("/",A1)-2,8),"none")

Format the cell as DATE

also, how can I have a cell display only company name and not the rest of
the text.

So what's the company name for this:

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100

Good luck with that!


--
Biff
Microsoft Excel MVP


"Iguss" wrote in message
...

Can someone please advise, how can I extract a date from a cell which
contains a date and a text.

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100

FEDERAL NATL MTG ASSN BNCHMRK 3

I would like to have one cell contain a date 11/11/07 if it is there else
"none callable is there's no date.

FEDERAL NATL MTG ASSN BNCHMRK 3

also, how can I have a cell display only company name and not the rest of
the text.

FEDERAL HOME LN BKS

FEDERAL NATL MTG ASSN

Thank you in advance for your help

Cell containgn a date and a text
 

I don't think you're going to be able to extract the company name *unless*
there is some recognizable pattern.

With all the samples you posted the name is before either CONS or BNCHMRK.
Does that pattern exist in all of your entries?

--
Biff
Microsoft Excel MVP


"Iguss" wrote in message
...
Thank you so much.

Names are,

FEDERAL FARM CR BKS CONS CONS B Callable 05/01/08@100
Federal Farm Credit Bank

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100
Federal Home Loan Bank

FEDERAL HOME LN BKS CONS BD 6.6
Federal Home Loan Bank

FEDERAL NATL MTG ASSN BNCHMRK 3
Federal National Mortgage Association

Thank you again

"T. Valko" wrote:

Assuming that the date format is *always* the same: mm/dd/yy, *AND* there
are no other "/" within the string.

=IF(COUNT(FIND("/",A1)),--MID(A1,FIND("/",A1)-2,8),"none")

Format the cell as DATE

also, how can I have a cell display only company name and not the rest
of
the text.

So what's the company name for this:

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100

Good luck with that!


--
Biff
Microsoft Excel MVP


"Iguss" wrote in message
...

Can someone please advise, how can I extract a date from a cell which
contains a date and a text.

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100

FEDERAL NATL MTG ASSN BNCHMRK 3

I would like to have one cell contain a date 11/11/07 if it is there
else
"none callable is there's no date.

FEDERAL NATL MTG ASSN BNCHMRK 3

also, how can I have a cell display only company name and not the rest
of
the text.

FEDERAL HOME LN BKS

FEDERAL NATL MTG ASSN

Thank you in advance for your help

Cell containgn a date and a text
 

These are the only name that are in my list all the time, and Yes to your
question.

Thank you

"T. Valko" wrote:

I don't think you're going to be able to extract the company name *unless*
there is some recognizable pattern.

With all the samples you posted the name is before either CONS or BNCHMRK.
Does that pattern exist in all of your entries?

--
Biff
Microsoft Excel MVP


"Iguss" wrote in message
...
Thank you so much.

Names are,

FEDERAL FARM CR BKS CONS CONS B Callable 05/01/08@100
Federal Farm Credit Bank

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100
Federal Home Loan Bank

FEDERAL HOME LN BKS CONS BD 6.6
Federal Home Loan Bank

FEDERAL NATL MTG ASSN BNCHMRK 3
Federal National Mortgage Association

Thank you again

"T. Valko" wrote:

Assuming that the date format is *always* the same: mm/dd/yy, *AND* there
are no other "/" within the string.

=IF(COUNT(FIND("/",A1)),--MID(A1,FIND("/",A1)-2,8),"none")

Format the cell as DATE

also, how can I have a cell display only company name and not the rest
of
the text.

So what's the company name for this:

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100

Good luck with that!


--
Biff
Microsoft Excel MVP


"Iguss" wrote in message
...

Can someone please advise, how can I extract a date from a cell which
contains a date and a text.

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100

FEDERAL NATL MTG ASSN BNCHMRK 3

I would like to have one cell contain a date 11/11/07 if it is there
else
"none callable is there's no date.

FEDERAL NATL MTG ASSN BNCHMRK 3

also, how can I have a cell display only company name and not the rest
of
the text.

FEDERAL HOME LN BKS

FEDERAL NATL MTG ASSN

Thank you in advance for your help

Cell containgn a date and a text
 

See reply at your other post.

--
Biff
Microsoft Excel MVP


"Iguss" wrote in message
...
These are the only name that are in my list all the time, and Yes to your
question.

Thank you

"T. Valko" wrote:

I don't think you're going to be able to extract the company name
*unless*
there is some recognizable pattern.

With all the samples you posted the name is before either CONS or
BNCHMRK.
Does that pattern exist in all of your entries?

--
Biff
Microsoft Excel MVP


"Iguss" wrote in message
...
Thank you so much.

Names are,

FEDERAL FARM CR BKS CONS CONS B Callable 05/01/08@100
Federal Farm Credit Bank

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100
Federal Home Loan Bank

FEDERAL HOME LN BKS CONS BD 6.6
Federal Home Loan Bank

FEDERAL NATL MTG ASSN BNCHMRK 3
Federal National Mortgage Association

Thank you again

"T. Valko" wrote:

Assuming that the date format is *always* the same: mm/dd/yy, *AND*
there
are no other "/" within the string.

=IF(COUNT(FIND("/",A1)),--MID(A1,FIND("/",A1)-2,8),"none")

Format the cell as DATE

also, how can I have a cell display only company name and not the
rest
of
the text.

So what's the company name for this:

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100

Good luck with that!


--
Biff
Microsoft Excel MVP


"Iguss" wrote in message
...

Can someone please advise, how can I extract a date from a cell
which
contains a date and a text.

FEDERAL HOME LN BKS CONS BD 3%0 Callable 11/11/07@100

FEDERAL NATL MTG ASSN BNCHMRK 3

I would like to have one cell contain a date 11/11/07 if it is there
else
"none callable is there's no date.

FEDERAL NATL MTG ASSN BNCHMRK 3

also, how can I have a cell display only company name and not the
rest
of
the text.

FEDERAL HOME LN BKS

FEDERAL NATL MTG ASSN

Thank you in advance for your help