Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting text from a string
I have a spreadsheet which imports data in the form R/IVE/0458 I need
to extract data to read IVE 0458 an added complication being that some times there are 5 digits ie R/IVE/40755 which needs to read IVE 40755 Hope this makes sense, thanks in anticipation Martin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting text from a string
Try:
=MID(A1,FIND("/",A1)+1,255) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk " wrote in message ... I have a spreadsheet which imports data in the form R/IVE/0458 I need to extract data to read IVE 0458 an added complication being that some times there are 5 digits ie R/IVE/40755 which needs to read IVE 40755 Hope this makes sense, thanks in anticipation Martin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting text from a string
Hi,
You could use Edit Replace, but you'd have to use it twice. 1st time: Replace R/ with nothing. Replace all. 2nd time: Replace / with a space. Replace all. Regards - Dave |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting text from a string
On Mon, 2 Jun 2008 12:42:31 -0700 (PDT), "
wrote: I have a spreadsheet which imports data in the form R/IVE/0458 I need to extract data to read IVE 0458 an added complication being that some times there are 5 digits ie R/IVE/40755 which needs to read IVE 40755 Hope this makes sense, thanks in anticipation Martin If all of your desired data begins after R/, then: =SUBSTITUTE(MID(A1,3,255),"/"," ") If all of your desired data begins after the first "/", and there can be more than one character before it, then: =SUBSTITUTE(MID(A1,FIND("/",A1)+1,255),"/"," ") --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting text from a string
With your extracted value in A1
Try this: B1: =MID(SUBSTITUTE(A1,"/"," "),3,255) Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel " wrote in message ... I have a spreadsheet which imports data in the form R/IVE/0458 I need to extract data to read IVE 0458 an added complication being that some times there are 5 digits ie R/IVE/40755 which needs to read IVE 40755 Hope this makes sense, thanks in anticipation Martin |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting text from a string
On Mon, 2 Jun 2008 12:42:31 -0700 (PDT), "
wrote: I have a spreadsheet which imports data in the form R/IVE/0458 I need to extract data to read IVE 0458 an added complication being that some times there are 5 digits ie R/IVE/40755 which needs to read IVE 40755 Hope this makes sense, thanks in anticipation Martin Try this: =SUBSTITUTE(SUBSTITUTE(A1,"R/",""),"/"," ") This first replaces the R/ with nothing and then replace the / with a a single space. Hope this helps / Lars-Åke |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting text from a string
On Jun 2, 9:04*pm, Ron Rosenfeld wrote:
On Mon, 2 Jun 2008 12:42:31 -0700 (PDT), " wrote: I have a spreadsheet which imports data in the form R/IVE/0458 I need to extract data to read IVE 0458 an added complication being that some times there are 5 digits ie R/IVE/40755 which needs to read IVE 40755 Hope this makes sense, thanks in anticipation Martin If all of your desired data begins after R/, then: =SUBSTITUTE(MID(A1,3,255),"/"," ") If all of your desired data begins after the first "/", and there can be more than one character before it, then: =SUBSTITUTE(MID(A1,FIND("/",A1)+1,255),"/"," ") --ron Thanks this worked a treat and will save me a lot of time at work Martin |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting text from a string
On Mon, 2 Jun 2008 13:49:26 -0700 (PDT), "
wrote: Thanks this worked a treat and will save me a lot of time at work Martin You're welcome. Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting text from string | Excel Worksheet Functions | |||
Extracting the last set of words from a text string | Excel Worksheet Functions | |||
extracting numbers within text string! | Excel Worksheet Functions | |||
Extracting integers from a text string. | Excel Worksheet Functions | |||
Extracting from a text string | Excel Worksheet Functions |