Find and extract text from cell

[David P.]

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.

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

2) Include all information starting from the first number 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)

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

 

[Ron Rosenfeld]

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

 

[David P.]

Many thanks to you both.
--
David P.

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