MS Excel, Stripping Out Char(10) Data [Solved/Closed]

Kieron - May 5, 2009 at 02:50 AM - Latest reply:  Gene
- Dec 13, 2010 at 01:20 PM
Hello,

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 seperate columns and a row per entry. So at the end, I should have 9 sperate columns, with 1 rows of data each.

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

Many Thanks

Kieron
See more 

8 replies

Best answer
6
Thank you
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.

Thank you, Hugh Askew 6

Something to say? Add comment

CCM has helped 1678 users this month

You are amazing! This really works!!!
Yes! This was exactly what I was looking for!
This gets my vote for Best Answer
3
Thank you
That comma is the separator for the csv format. CSV = Comma separated value. ANd the comma is actually the separator for a new column. (you can split the cell into different collumns using the Text to Columns option)

THen you need to change the text from one row/several columns to one column/several rows which is pretty simple as well.
Hi

I have exactly opposite problem. I have data in two cells, and I want to join them to one cell, with a line break in between. I tried =(A1 & Char(10) & A2), but it throws a Name? error

Can you please help?
Hi,

While doing above operation you might getting the square between data of A1 and A2, to execute it properly you have to check whether wrap text option is enable or not. If it is disable then enable it. For enabling wrap text select all the column which you want to use for this operation. Go to menu Format --> Cells --> Alignment --> Wrap text (Select the check box to enable the option) --> Click OK. And then you will find your data separated by line break

Regards
Raj Mishra
KIERON 2 Posts Monday May 4, 2009Registration date May 5, 2009 Last seen - May 5, 2009 at 06:07 AM
2
Thank you
Hi,

Firstly thanks for the reponse.

I am however a little confused. I am under the impression that in you example:

=SUBSTITUTE(F5,"*",",")

that;

F5 is the Range, "*" is something to do with the Alt-Enter and "," is replacing something with a comma.

Is this correct?

I am however, still confused with how this removes the Alt-Enter and displays the data onto different rows.

If you could give me a little more direction, I would be grateful.

Kieron
0
Thank you
KIERON 2

I am however a little confused. I am under the impression that in you example:
=SUBSTITUTE(F5,"*",",")
that;
F5 is the Range, "*" is something to do with the Alt-Enter and "," is replacing something with a comma.
Is this correct?
I am however, still confused with how this removes the Alt-Enter and displays the data onto different rows.
If you could give me a little more direction, I would be grateful.


Hi, Kieron -- here's what I did, based on Hugh's answer above:

In the top cell of the column next to the column that I wanted to separate (call it "B2"), I entered the following: =SUBSTITUTE(A2,CHAR(10),*); [that's not really an asterisk: it's the "alt-0149" dot] Then I simply copied that formula all the way down to the bottom row. That gave me a column in which all the "hard returns" from A2 were now represented by the Alt-0149 dot. Then I used Text to Columns with the dot as my separator (this keeps things from getting screwy if you actually have commas in your cells)