On Tue, 28 Mar 2006 07:07:19 -0500, Ron Rosenfeld
wrote:

On Tue, 28 Mar 2006 02:52:18 -0600, Portuga
wrote:


Hi,

Bellow I have examples of contents of cells with DWH fields.
in Cell A1 I have:

Srce_Inst
Period_Dte
Srce_Cr_Grade
Cr_Grade_Qlfr_Cde
(Each DWH field in cell A1 is separated by breaks (Alt+Enter)

In cell A2 I have:
Acc_Setup_Dte, Contract_Eff_Dte, Period_Dte
(Each DWH field in cell A2 is separated by comas)


My question is:
Can I parse the contents of the cells?
I want each DWH field of cells A1 and A2 to be in different cells:


B1:Srce_Inst
B2:Period_Dte
B2:Srce_Cr_Grade
B4:Cr_Grade_Qlfr_Cde
B5:Acc_Setup_Dte
B6:Contract_Eff_Dte
B7:Period_Dte


thanks!

To accomplish exactly what you describe is fairly simple. Using Longre's free
morefunc.xll add-in from http://xcell05.free.fr/ and use regular expressions,
which can easily parse out the DWH fields:

B1: =REGEX.MID($A$1,"\w+",ROW())
copy/drag down to B4

B5: =REGEX.MID($A$2,"\w+",ROWS($1:1))
copy/drag down to B7

But I suspect there may be variations both in total number of entries and total
number of "phrases" within each entry.

To handle a varying number of "phrases" within each entry, you could do the
following:

B1:
=IF(ROW()<=REGEX.COUNT($A$1,"\w+"),
REGEX.MID($A$1,"\w+",ROW()),REGEX.MID(
$A$2,"\w+",ROW()-REGEX.COUNT($A$1,"\w+")))

copy/drag down as far as required.

To handle a range with more entries would be more complex in formulas, and
perhaps more easily handled in VBA.


--ron

To expand the above, if the total number of characters in the range is less
than 256, then

B1:
=REGEX.MID(MCONCAT(rng,","),"\w+",ROWS($1:1))

and copy/drag down as far as needed.

rng is the Range that you wish to parse out into the column. In your example,
it would be $A$1:$A$2.


--ron