I have exported address information from Quickbooks to an Excel spreadsheet
and need to know how to do the following to split out the info into other
cells. The Text to Columns feature doesn't look like its going to help.
Here's what I need:

1) Find and include everything up to the third space (" ") from the left
2) Include all information starting from the first number from the left to
the first comma from the left

Many thanks for your help.

--
David P.

On Mon, 15 Sep 2008 17:09:00 -0700, David P.
wrote:

I have exported address information from Quickbooks to an Excel spreadsheet
and need to know how to do the following to split out the info into other
cells. The Text to Columns feature doesn't look like its going to help.
Here's what I need:

You can do it with worksheet functions:


1) Find and include everything up to the third space (" ") from the left

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3))-1)

2) Include all information starting from the first number from the left to
the first comma from the left


The formula below must be entered as an **array** formula. Hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula.

=MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT(
"1:255")),1)),0),FIND(",",A1)-MATCH(TRUE,ISNUMBER(-MID(
A1,ROW(INDIRECT("1:255")),1)),0))

A more flexible solution would have you download and install Longre's free
morefunc.xll add-in from http://xcell05.free.fr


Then use these regular expression formulas:

Up to the third space:

=REGEX.MID(A1,"^(\S+\s+){2}\S+")

From first digit to first comma:

=REGEX.MID(A1,"\d[^,]+")
--ron

2) Include all information starting from the first number from the left
to the first comma from the left

The formula below must be entered as an **array** formula. Hold
down <ctrl<shift while hitting <enter. Excel will place braces {...}
around the formula.

=MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT(
"1:255")),1)),0),FIND(",",A1)-MATCH(TRUE,ISNUMBER(-MID(
A1,ROW(INDIRECT("1:255")),1)),0))

A little shorter and normally entered....

=MID(LEFT(A1,FIND(",",A1&",")-1),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") ),255)

although it differs from yours at the extremes... if there is no number in
the text, my formula returns an empty string whereas yours returns an #N/A
error... if there is no comma in the text, my formula returns from the first
number to the end of the text whereas yours returns a #VALUE! error. These
differences may or may not be significant to the OP.

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Mon, 15 Sep 2008 17:09:00 -0700, David P.

wrote:

I have exported address information from Quickbooks to an Excel
spreadsheet
and need to know how to do the following to split out the info into other
cells. The Text to Columns feature doesn't look like its going to help.
Here's what I need:

You can do it with worksheet functions:


1) Find and include everything up to the third space (" ") from the left

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3))-1)

2) Include all information starting from the first number from the left to
the first comma from the left


The formula below must be entered as an **array** formula. Hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around
the
formula.

=MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT(
"1:255")),1)),0),FIND(",",A1)-MATCH(TRUE,ISNUMBER(-MID(
A1,ROW(INDIRECT("1:255")),1)),0))

A more flexible solution would have you download and install Longre's free
morefunc.xll add-in from http://xcell05.free.fr


Then use these regular expression formulas:

Up to the third space:

=REGEX.MID(A1,"^(\S+\s+){2}\S+")

From first digit to first comma:

=REGEX.MID(A1,"\d[^,]+")
--ron

On Tue, 16 Sep 2008 04:23:54 -0400, "Rick Rothstein"
wrote:

A little shorter and normally entered....

=MID(LEFT(A1,FIND(",",A1&",")-1),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") ),255)

although it differs from yours at the extremes... if there is no number in
the text, my formula returns an empty string whereas yours returns an #N/A
error... if there is no comma in the text, my formula returns from the first
number to the end of the text whereas yours returns a #VALUE! error. These
differences may or may not be significant to the OP.

The REGEX formulas I posted likewise return empty strings if the pattern
doesn't match.

The issue, of course, arises if the specification the OP posted turns out not
to be 100% accurate. For me, the regex expressions will be easier to change
and test.
--ron

Many thanks to you both.
--
David P.


"Ron Rosenfeld" wrote:

On Tue, 16 Sep 2008 04:23:54 -0400, "Rick Rothstein"
wrote:

A little shorter and normally entered....

=MID(LEFT(A1,FIND(",",A1&",")-1),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") ),255)

although it differs from yours at the extremes... if there is no number in
the text, my formula returns an empty string whereas yours returns an #N/A
error... if there is no comma in the text, my formula returns from the first
number to the end of the text whereas yours returns a #VALUE! error. These
differences may or may not be significant to the OP.

The REGEX formulas I posted likewise return empty strings if the pattern
doesn't match.

The issue, of course, arises if the specification the OP posted turns out not
to be 100% accurate. For me, the regex expressions will be easier to change
and test.
--ron