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: Nikki on 14 May 2010 16:15 What formula could I use to pull 03275 from the below contents of a cell? EA_1045_02949_03275 (John Doe)
From: Brad on 14 May 2010 16:28 one way =right(a1,5) This assume that your information is in a1 -- Wag more, bark less "Nikki" wrote: > What formula could I use to pull 03275 from the below contents of a cell? > > EA_1045_02949_03275 (John Doe) >
From: Reeza on 14 May 2010 16:40 On May 14, 1:15 pm, Nikki <Ni...(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) If (John Doe) is part of the text..... =MID(B10, LEN(B10)-FIND(" (", B10,1)+6, 5) If the _XXXXXX is variable length you need a different approach though.
From: Jim Thomlinson on 14 May 2010 16:40 Assuming the format and text lenght of the ID to always be the same then =MID(A1, 15, 5) If you need to seach for the text between the last _ and the first balnk then we need to get a bit fancy... -- HTH... Jim Thomlinson "Nikki" wrote: > What formula could I use to pull 03275 from the below contents of a cell? > > EA_1045_02949_03275 (John Doe) >
From: Jim Thomlinson on 14 May 2010 16:43
Here is the fancy version... =LEFT(MID(A1, FIND("^", SUBSTITUTE(A1, "_", "^", LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))+1, 256), FIND(" ", MID(A1, FIND("^", SUBSTITUTE(A1, "_", "^", LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))+1, 256))) -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: > Assuming the format and text lenght of the ID to always be the same then > =MID(A1, 15, 5) > If you need to seach for the text between the last _ and the first balnk > then we need to get a bit fancy... > -- > HTH... > > Jim Thomlinson > > > "Nikki" wrote: > > > What formula could I use to pull 03275 from the below contents of a cell? > > > > EA_1045_02949_03275 (John Doe) > > |