New functions needed
in [Programming]
|
From: Jacob Skaria on 27 May 2010 16:16 --It is separating the string with " SW " as delimiter and getting the portion to the left most portion of the string. --Then it removes the string in cell B1 from the left which ends up with the address -- Jacob (MVP - Excel) "Jim Berglund" wrote: > Thanks Jacob. I got this to work, and I understand the first line, but I do > have a few more questions... > > In Cell C1, what is going on? > > Jim > > "Jacob Skaria" <JacobSkaria(a)discussions.microsoft.com> wrote in message > news:69C98F6C-EC5A-44BC-95A3-383BC404D3A4(a)microsoft.com... > > You can achieve the same with a combination of functions. With your text > > in > > cell A1 try the below formulas. > > > > 'in cell B1 > > =LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1) > > > > 'in cell C1 > > =MID(TRIM(LEFT(SUBSTITUTE(A1 & " "," SW ",REPT(" ",255)),255)), > > LEN(B1)+1,255) > > > > -- > > Jacob (MVP - Excel) > > > > > > "Jim Berglund" wrote: > > > >> Consider the following entries in Column A1: > >> > >> CHRISTIAN 32 PATTERSON ME SW > >> TOM & PAT 235 PROMINENCE HGTS SW CALG > >> M 26 PATTERSON CL SW CALG > >> L P 54 PATTERSON PK SW CA > >> ROBERTO JOSE 27 PATTERSON PL SW CAL > >> > >> I want to parse this out. Here's here are the columns I want: > >> B: Left(A1, find the first number)-2) ' Get the first name, first > >> name plus initial, initial(s), or first and middle names > >> C: Mid(A1, find the first number, Find("SW", A1) 'Get the Address > >> > >> Is there any way of writing this so that it works? Is it unreasonable to > >> want a simple function for doing this, since everyone in business must > >> want > >> to do this at some point in their lives? > >> > >> Another useful function would be a BETWEEN function. > >> > >> =BETWEEN(find the first number,"SW", A1) > >> > >> Musing... > >> Jim Berglund > >> > >> > >> > >> > >> . > >> > . > |