Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting right part of text string
Hi
I have cells that are populated by a data dump from a work management system and the text will always appear in a preformatted manner eg: VODAFONE P3 INC526642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 I want to select the date only from this string - which will always appear after the word DUE .. I have tried the formula below (from this forum) though am coming unstuck and it is not returning what I want. =RIGHT(R2,FIND("DUE ",R2&"DUE ")) The above formula gives me this result ... 26642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 TIA ... -- Sue Compelling |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting right part of text string
=RIGHT(R2,LEN(R2)-FIND("DUE ",R2)-3)
On 4 Lis, 09:28, Sue Compelling wrote: Hi I have cells that are populated by a data dump from a work management system and the text will always appear in a preformatted manner eg: *VODAFONE P3 INC526642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 I want to select the date only from this string - which will always appear after the word DUE .. I have tried the formula below (from this forum) though am coming unstuck and it is not returning what I want. =RIGHT(R2,FIND("DUE ",R2&"DUE ")) The above formula gives me this result *... 26642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 TIA ... -- Sue Compelling |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting right part of text string
Hi Sue,
try this =TRIM(MID(R2,SEARCH("due",R2)+3,9)) Mike "Sue Compelling" wrote: Hi I have cells that are populated by a data dump from a work management system and the text will always appear in a preformatted manner eg: VODAFONE P3 INC526642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 I want to select the date only from this string - which will always appear after the word DUE .. I have tried the formula below (from this forum) though am coming unstuck and it is not returning what I want. =RIGHT(R2,FIND("DUE ",R2&"DUE ")) The above formula gives me this result ... 26642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 TIA ... -- Sue Compelling |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting right part of text string
Hi,
Looking at this again it seems you may want the time also so if that's the case use this =TRIM(MID(R2,SEARCH("due",R2)+3,256)) Mike "Mike H" wrote: Hi Sue, try this =TRIM(MID(R2,SEARCH("due",R2)+3,9)) Mike "Sue Compelling" wrote: Hi I have cells that are populated by a data dump from a work management system and the text will always appear in a preformatted manner eg: VODAFONE P3 INC526642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 I want to select the date only from this string - which will always appear after the word DUE .. I have tried the formula below (from this forum) though am coming unstuck and it is not returning what I want. =RIGHT(R2,FIND("DUE ",R2&"DUE ")) The above formula gives me this result ... 26642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 TIA ... -- Sue Compelling |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting right part of text string
Its a bit lenghty; but should work for different date formats..
=--LEFT(SUBSTITUTE(MID(A1,SEARCH(" DUE ",A1&" DUE ")+5,255), " ",REPT(" ",255)),12) If this post helps click Yes --------------- Jacob Skaria "Sue Compelling" wrote: Hi I have cells that are populated by a data dump from a work management system and the text will always appear in a preformatted manner eg: VODAFONE P3 INC526642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 I want to select the date only from this string - which will always appear after the word DUE .. I have tried the formula below (from this forum) though am coming unstuck and it is not returning what I want. =RIGHT(R2,FIND("DUE ",R2&"DUE ")) The above formula gives me this result ... 26642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 TIA ... -- Sue Compelling |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting right part of text string
Hi,
Try this =INT(RIGHT(B14,LEN(B14)-SEARCH("Due",B14)-3)) Format the cell as date. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Sue Compelling" wrote in message ... Hi I have cells that are populated by a data dump from a work management system and the text will always appear in a preformatted manner eg: VODAFONE P3 INC526642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 I want to select the date only from this string - which will always appear after the word DUE .. I have tried the formula below (from this forum) though am coming unstuck and it is not returning what I want. =RIGHT(R2,FIND("DUE ",R2&"DUE ")) The above formula gives me this result ... 26642 SITE: SOJ Shotover Jet HQ DUE 4/11/09 16:09 TIA ... -- Sue Compelling |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete part of a text string | Excel Worksheet Functions | |||
Extract part of a text string | Excel Worksheet Functions | |||
Omitting right part of text string | Excel Worksheet Functions | |||
How do I extract part of a text string | Excel Discussion (Misc queries) | |||
Use a formula to delete part of a text string? | Excel Worksheet Functions |