| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find text in free-format text field | Excel Discussion (Misc queries) | |||
| Find repeated text in a text document in Excel | Excel Worksheet Functions | |||
| Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
| Find text within cell then display text to left | Excel Discussion (Misc queries) | |||
| can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
Linear Mode
