[MS Excel] - Stripping Out Char(10) Data

Published by aakai1056 - Last update on November 21, 2009 11:39 AM by aakai1056

[MS Excel] - Stripping Out Char(10) Data


Issue



I working in Excel with VBA and have 5 or 6 rows of data located within 1 cell. The user has decided to use Alt-Enter whilst entering the data.

The data is as follows:

Encoding: MPEG 1.0 layer 3
Mode: 44100 HZ Stereo
Track Number: 2
Title: On My Knees
Artist: Jaci Valasquez
Album: On My Knees: The Best Of Jaci Valasquez
Year: 2006
Genre: Gospol
Comment:

I wish to extract all of this information onto separate columns and a row per entry. So at the end, I should have 9 separate columns, with 1 row of data each.

Is this possible and I would be grateful of any help.

Solution


Firstly, make a copy of your worksheet to practice on.
Then, use the Substitute function.

=SUBSTITUTE(F5,"*",",") - except instead of the asterisk, hit ALT-Enter.
(this forces a line break)
The formula results will have the char(10) replaced with a comma.
If there are multiple instances of the line breaks in your text, leave the last argument in the SUBSTITUTE function out,
That way, all instances will be replaced.

Next, do Copy>Paste Special>Values.
Then go to Data>Text to Columns.
Click the Commas button, Finish.

Note


Thanks to Hugh Askew for this tip on the forum.
See also
supinfo