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

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




.

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




.

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

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

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




.

.