Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
New functions needed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
New functions needed
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&"0123 456789"))-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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
New functions needed
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" wrote in message ... 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&"0123 456789"))-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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
New functions needed
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
New functions needed
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" wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
New functions needed
--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" wrote in message ... 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&"0123 456789"))-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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help in EXCEL functions - needed badly | New Users to Excel | |||
help needed with lookup and related functions | Excel Worksheet Functions | |||
Major help needed - Iterative Functions | Excel Programming | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Index and match functions help needed. | Excel Worksheet Functions |