ExcelBanter - Find Text Within Text

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Find Text Within Text (https://www.excelbanter.com/excel-worksheet-functions/235610-find-text-within-text.html)

Cathy Landry

Find Text Within Text

Hello,

We have concatenated data loaded into Oracle in att 5. I have exported this
to Excel and now need to pull out the transaction dates and put them into
another column. The data below shows what is contained in one cell and the
transaction date is the second date listed that I need pulled into a separate
cell in Excel

Any help would be appreciated!

24493989153026554078566-1_629430_198_7661_06/02/2009_06/01/2009_06/10/2009_06/16/2009_384149_2361257_3487911_72703_584423_E1C73691_ 4789

Thank you!
Cathy

Mike H

Find Text Within Text

Hi,

It may be as simple as this if all your strings are the same length

=MID(A1,30,10)

Mike

"Cathy Landry" wrote:

Hello,

We have concatenated data loaded into Oracle in att 5. I have exported this
to Excel and now need to pull out the transaction dates and put them into
another column. The data below shows what is contained in one cell and the
transaction date is the second date listed that I need pulled into a separate
cell in Excel

Any help would be appreciated!

24493989153026554078566-1_629430_198_7661_06/02/2009_06/01/2009_06/10/2009_06/16/2009_384149_2361257_3487911_72703_584423_E1C73691_ 4789

Thank you!
Cathy

Cathy Landry

Find Text Within Text

Hi Mike,

Thank you for the super quick response!! Unfortunately, not every line will
not be the same length however the transaction date field will always be the
second date field referenced.

"Mike H" wrote:

Hi,

It may be as simple as this if all your strings are the same length

=MID(A1,30,10)

Mike

"Cathy Landry" wrote:

Hello,

We have concatenated data loaded into Oracle in att 5. I have exported this
to Excel and now need to pull out the transaction dates and put them into
another column. The data below shows what is contained in one cell and the
transaction date is the second date listed that I need pulled into a separate
cell in Excel

Any help would be appreciated!

24493989153026554078566-1_629430_198_7661_06/02/2009_06/01/2009_06/10/2009_06/16/2009_384149_2361257_3487911_72703_584423_E1C73691_ 4789

Thank you!
Cathy

T. Valko

Find Text Within Text

Assuming the date of interest is *always* immediately after the 5th
underscore and is *always* in the format mm/dd/yyyy.

=--MID(A1,FIND("^^",SUBSTITUTE(A1,"_","^^",5))+1,10)

Format as Date

--
Biff
Microsoft Excel MVP

"Cathy Landry" wrote in message
...
Hi Mike,

Thank you for the super quick response!! Unfortunately, not every line
will
not be the same length however the transaction date field will always be
the
second date field referenced.

"Mike H" wrote:

Hi,

It may be as simple as this if all your strings are the same length

=MID(A1,30,10)

Mike

"Cathy Landry" wrote:

Hello,

We have concatenated data loaded into Oracle in att 5. I have exported
this
to Excel and now need to pull out the transaction dates and put them
into
another column. The data below shows what is contained in one cell and
the
transaction date is the second date listed that I need pulled into a
separate
cell in Excel

Any help would be appreciated!

24493989153026554078566-1_629430_198_7661_06/02/2009_06/01/2009_06/10/2009_06/16/2009_384149_2361257_3487911_72703_584423_E1C73691_ 4789

Thank you!
Cathy

Mike H

Find Text Within Text

Hi,

Here's another stab. does it always have the same amount of underscores
before the date?

=MID(MID(MID(SUBSTITUTE(A1,"_","^",5),1,256),FIND( "^",SUBSTITUTE(A1,"_","^",5)),256),2,FIND("_",MID( MID(SUBSTITUTE(A1,"_","^",5),1,256),FIND("^",SUBST ITUTE(A1,"_","^",5)),256))-2)

Mike

"Cathy Landry" wrote:

Hi Mike,

Thank you for the super quick response!! Unfortunately, not every line will
not be the same length however the transaction date field will always be the
second date field referenced.

"Mike H" wrote:

Hi,

It may be as simple as this if all your strings are the same length

=MID(A1,30,10)

Mike

"Cathy Landry" wrote:

Hello,

We have concatenated data loaded into Oracle in att 5. I have exported this
to Excel and now need to pull out the transaction dates and put them into
another column. The data below shows what is contained in one cell and the
transaction date is the second date listed that I need pulled into a separate
cell in Excel

Any help would be appreciated!

24493989153026554078566-1_629430_198_7661_06/02/2009_06/01/2009_06/10/2009_06/16/2009_384149_2361257_3487911_72703_584423_E1C73691_ 4789

Thank you!
Cathy

Cathy Landry

Find Text Within Text

You guys Rock!!!

Works perfectly.........yeah.......Thank you!!!

"T. Valko" wrote:

Assuming the date of interest is *always* immediately after the 5th
underscore and is *always* in the format mm/dd/yyyy.

=--MID(A1,FIND("^^",SUBSTITUTE(A1,"_","^^",5))+1,10)

Format as Date

--
Biff
Microsoft Excel MVP

"Cathy Landry" wrote in message
...
Hi Mike,

Thank you for the super quick response!! Unfortunately, not every line
will
not be the same length however the transaction date field will always be
the
second date field referenced.

"Mike H" wrote:

Hi,

It may be as simple as this if all your strings are the same length

=MID(A1,30,10)

Mike

"Cathy Landry" wrote:

Hello,

We have concatenated data loaded into Oracle in att 5. I have exported
this
to Excel and now need to pull out the transaction dates and put them
into
another column. The data below shows what is contained in one cell and
the
transaction date is the second date listed that I need pulled into a
separate
cell in Excel

Any help would be appreciated!

24493989153026554078566-1_629430_198_7661_06/02/2009_06/01/2009_06/10/2009_06/16/2009_384149_2361257_3487911_72703_584423_E1C73691_ 4789

Thank you!
Cathy

T. Valko

Find Text Within Text

You're wecome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Cathy Landry" wrote in message
...
You guys Rock!!!

Works perfectly.........yeah.......Thank you!!!

"T. Valko" wrote:

Assuming the date of interest is *always* immediately after the 5th
underscore and is *always* in the format mm/dd/yyyy.

=--MID(A1,FIND("^^",SUBSTITUTE(A1,"_","^^",5))+1,10)

Format as Date

--
Biff
Microsoft Excel MVP

"Cathy Landry" wrote in message
...
Hi Mike,

Thank you for the super quick response!! Unfortunately, not every line
will
not be the same length however the transaction date field will always
be
the
second date field referenced.

"Mike H" wrote:

Hi,

It may be as simple as this if all your strings are the same length

=MID(A1,30,10)

Mike

"Cathy Landry" wrote:

Hello,

We have concatenated data loaded into Oracle in att 5. I have
exported
this
to Excel and now need to pull out the transaction dates and put them
into
another column. The data below shows what is contained in one cell
and
the
transaction date is the second date listed that I need pulled into a
separate
cell in Excel

Any help would be appreciated!

24493989153026554078566-1_629430_198_7661_06/02/2009_06/01/2009_06/10/2009_06/16/2009_384149_2361257_3487911_72703_584423_E1C73691_ 4789

Thank you!
Cathy

Rick Rothstein

Find Text Within Text

I would assume this would work as well...

=--MID(A1,FIND("/",A1)+9,10)

--
Rick (MVP - Excel)

"T. Valko" wrote in message
...
Assuming the date of interest is *always* immediately after the 5th
underscore and is *always* in the format mm/dd/yyyy.

=--MID(A1,FIND("^^",SUBSTITUTE(A1,"_","^^",5))+1,10)

Format as Date

--
Biff
Microsoft Excel MVP

"Cathy Landry" wrote in message
...
Hi Mike,

Thank you for the super quick response!! Unfortunately, not every line
will
not be the same length however the transaction date field will always be
the
second date field referenced.

"Mike H" wrote:

Hi,

It may be as simple as this if all your strings are the same length

=MID(A1,30,10)

Mike

"Cathy Landry" wrote:

Hello,

We have concatenated data loaded into Oracle in att 5. I have exported
this
to Excel and now need to pull out the transaction dates and put them
into
another column. The data below shows what is contained in one cell and
the
transaction date is the second date listed that I need pulled into a
separate
cell in Excel

Any help would be appreciated!

24493989153026554078566-1_629430_198_7661_06/02/2009_06/01/2009_06/10/2009_06/16/2009_384149_2361257_3487911_72703_584423_E1C73691_ 4789

Thank you!
Cathy

T. Valko

Find Text Within Text

Yeah, that should work as well. I didn't think of looking for the slash in
the first date.

--
Biff
Microsoft Excel MVP

"Rick Rothstein" wrote in message
...
I would assume this would work as well...

=--MID(A1,FIND("/",A1)+9,10)

--
Rick (MVP - Excel)

"T. Valko" wrote in message
...
Assuming the date of interest is *always* immediately after the 5th
underscore and is *always* in the format mm/dd/yyyy.

=--MID(A1,FIND("^^",SUBSTITUTE(A1,"_","^^",5))+1,10)

Format as Date

--
Biff
Microsoft Excel MVP

"Cathy Landry" wrote in message
...
Hi Mike,

Thank you for the super quick response!! Unfortunately, not every line
will
not be the same length however the transaction date field will always be
the
second date field referenced.

"Mike H" wrote:

Hi,

It may be as simple as this if all your strings are the same length

=MID(A1,30,10)

Mike

"Cathy Landry" wrote:

Hello,

We have concatenated data loaded into Oracle in att 5. I have
exported this
to Excel and now need to pull out the transaction dates and put them
into
another column. The data below shows what is contained in one cell
and the
transaction date is the second date listed that I need pulled into a
separate
cell in Excel

Any help would be appreciated!

24493989153026554078566-1_629430_198_7661_06/02/2009_06/01/2009_06/10/2009_06/16/2009_384149_2361257_3487911_72703_584423_E1C73691_ 4789

Thank you!
Cathy

All times are GMT +1. The time now is 07:19 PM.