Parse contents of cell
 

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!


--
Portuga
------------------------------------------------------------------------
Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385
View this thread: http://www.excelforum.com/showthread...hreadid=527056

Parse contents of cell
 

Hi, I only have a work-around but maybe that'll help you until someone
can give you the real answer!

I usually paste the whole thing into Word and do a "find / replace"
there and paste it back into Excel.

Copy the cells into Word, each cell will appear in a table. That's
fine. Edit -- Replace. In the "find" part, type ^l (small L) and in
the "replace" part type ^t and click on "replace all". Select the whole
thing and, still in Word, cut and paste special as unformatted text.

You can now copy this back into Excel and should have the desired
effect!


--
HierkommtdieMau
------------------------------------------------------------------------
HierkommtdieMau's Profile: http://www.excelforum.com/member.php...o&userid=32867
View this thread: http://www.excelforum.com/showthread...hreadid=527056

Parse contents of cell
 

Maybe you can try this:

If your data is in col A1 and down enter this in col B1 and down:

=IF(ISERROR(SEARCH(CHAR(10),A1,1)),A1,SUBSTITUTE(A 1,CHAR(10),","))

This assumes that in the lines with Alt+Enter there are no commas.

Optionally copy col B and paste special--values over col A.

Then highlight your data in B (or A if you chose to do the previous
step) and use Data--Text to columns and use comma as the separator.

This will result in your values being spread across the columns. You
can then highlight those data and do a copy and paste special--values
to the final destination with the transpose option checked.

Regards

Hans

Parse contents of cell
 

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

Parse contents of cell
 

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