New functions needed
in [Programming]
|
From: Jim Berglund on 25 May 2010 01:11 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
From: Jacob Skaria on 25 May 2010 02:29 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 > > > > > . >
From: Jim Berglund on 25 May 2010 19:40 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 >> >> >> >> >> . >>
From: BillyBob on 25 May 2010 19:47 Jim, Why did you submit a new post about the same problem you inquired about in your other thread with subject "Parsing a string." In fact, you posted to that other thread 45 minutes before starting this post. Why ? BB "Jim Berglund" <jazzzbo(a)shaw.ca> wrote in message news:Obwn9i8%23KHA.3880(a)TK2MSFTNGP04.phx.gbl... > 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 > > > > >
From: Jim Berglund on 25 May 2010 21:05
I have had difficulty in asking the question in the right way, and as I try different ways to approach the problem, I get new ideas. I realized that I hadn't asked the question in a way that would get me an answer I could work with so I thought this re-phrasing might get me the answer I wanted - how to find a number in a string. I'm not a regular programmer, and am not familiar with the protocols of newsgroups. If I've made an error, I apologize. Jim "BillyBob" <billybob(a)has.onetooth> wrote in message news:E4BFF83D-A1B5-4E2F-8955-A39EE8A9DE6C(a)microsoft.com... > Jim, > > Why did you submit a new post about the same problem you inquired about in > your other thread with subject "Parsing a string." In fact, you posted to > that other thread 45 minutes before starting this post. Why ? > > BB > > "Jim Berglund" <jazzzbo(a)shaw.ca> wrote in message > news:Obwn9i8%23KHA.3880(a)TK2MSFTNGP04.phx.gbl... >> 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 >> >> >> >> >> > |