MS Excel, Stripping Out Char(10) Data
Solved/Closed
Related:
- MS Excel, Stripping Out Char(10) Data
- Transfer data from one excel worksheet to another automatically - Guide
- Ms excel free download - Download - Spreadsheets
- Tmobile data check - Guide
- Excel date format dd.mm.yyyy - Guide
- Number to words in excel - Guide
4 responses
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.
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.
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.
THen you need to change the text from one row/several columns to one column/several rows which is pretty simple as well.
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
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
Posts
2
Registration date
Monday May 4, 2009
Status
Member
Last seen
May 5, 2009
2
May 5, 2009 at 06:07 AM
May 5, 2009 at 06:07 AM
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
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
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)
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)
Sep 3, 2009 at 09:21 AM
Dec 13, 2010 at 01:00 PM
This gets my vote for Best Answer