Formula Question
in [General]
|
Prev: Press Enter and Move Back to Column A Automatically
Next: Copy filename from Excel 2010 Recent Workbooks to Clipboard
From: Lars-�ke Aspelin on 14 May 2010 16:45 On Fri, 14 May 2010 13:15:01 -0700, Nikki <Nikki(a)discussions.microsoft.com> wrote: >What formula could I use to pull 03275 from the below contents of a cell? > >EA_1045_02949_03275 (John Doe) The following formula will pull characters 15 to 19 from the string in A1: =MID(A1,15,5) Is that what you need? The following formula will pull all characters between the rightmost "_" and the next " ", assuming the result is within the 30 first characters of the string: =MID(A1,MAX(IF(MID(A1,ROW(1:30),1)="_",ROW(1:30)))+1,FIND(" ", MID(A1,MAX(IF(MID(A1,ROW(1:30),1)="_",ROW(1:30)))+1, MAX(IF(MID(A1,ROW(1:30),1)="_",ROW(1:30)))+1))-1) Note this is an array formula that must be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Is that what you want? As you see, you have to provide more information on the possible format of the original string in order not to have us to guess what you mean and to propose a formula that is as simple as possible, but still working. Hope this helps / Lars-�ke
From: Ron Rosenfeld on 14 May 2010 20:02 On Fri, 14 May 2010 13:15:01 -0700, Nikki <Nikki(a)discussions.microsoft.com> wrote: >What formula could I use to pull 03275 from the below contents of a cell? > >EA_1045_02949_03275 (John Doe) To extract the value between the last underscore, and the following <space>: =TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE( A1,"_",REPT(" ",99)),99))," ",REPT(" ",99)),99)) --ron
From: Shane Devenshire on 15 May 2010 17:49
Don't need a formula: 1. Select the data 2. Choose Data, Text to Columns 3. Select Delimited, Next 4. Check Space and add _ to Other, then click Next 5. Highlight each column in the Data Preview pane that you don't want and turn on the option Do not import (Skip) and click finish. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Nikki" wrote: > What formula could I use to pull 03275 from the below contents of a cell? > > EA_1045_02949_03275 (John Doe) > |