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