Search : in
By :

MS Excel, Stripping Out Char(10) Data

Last answer on Nov 20, 2009 1:05:03 am GMT Kieron, on May 5, 2009 7:50:18 am BST 
 Report this message to moderators

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

Configuration: Windows XP Internet Explorer 7.0

Best answers for « MS Excel, Stripping Out Char(10) Data » in :
[MS Excel] - Stripping Out Char(10) Data Show [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...
Using excel if function with data validation Show
Excel - Check rows for data, copy to new (Solved) Show
Publish a PDF file on your website ShowPublish a PDF file on your Website Displaying a link to a PDF on your Website Displaying the PDF on your website Displaying a link to a PDF on your Website Proposing a PDF link on your own website is an operation that can seem...
Keyboard Shortcuts for Firefox ShowKeyboard Shortcuts for Firefox Tabs Open a new tabs To change tabs To close a tab To Restore a tab Windows New Window Changing a window Closing a window Navigation Previous and Next Reloading Stopping a page from loading View Font...
SKU011.CAB missing ShowSKU011.CAB missing Issue When opening MS Word or MS Excel, you are prompted by the following error message: SKU011.CAB missing No installation Cd available. Solution To solve this problem: Regedit (start menu -> run -> type...
Download Excel Password Recovery Master Show
Download Excel Viewer Show
Download EMS Data Export for SQL Server ShowDo you need to analyze your data in your SQL Server database? Would you like to print them? EMS Data Export for SQL Server offers to export them into MS Access, MS Excel, MS Word, RTF, HTML, XML, PDF, TXT, CSV, DBF or ODF only in a few steps. This...
MS DOS Files and directories ShowFiles in MS DOS In a computers data is stored in files. When you run a program, MS-DOS processes the data stored in the file and passes it to the system. In MS-DOS a file can be any size, however the file name is more restricted, it can only have...
Spreadsheets - Conditional expressions ShowWhat is a Conditional Structure? Conditional structures are instructions that allow to test if a condition is true or not. Conditional structures may be associated together. To successfully execute these tests using logical functions, spreadsheets...
Ask your question Reply

1

Hugh Askew, on May 5, 2009 9:43:03 am BST
  • +1

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.

   
Reply to Hugh Askew

4

tina, on Sep 3, 2009 2:21:59 pm BST

You are amazing! This really works!!!

   
Reply to tina

2

KIERON, on May 5, 2009 11:07:36 am BST
  • +1

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

   
Reply to KIERON

3

mohamed, on Jul 23, 2009 2:32:45 pm BST
  • +2

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.

   
Reply to mohamed

5

vid, on Sep 25, 2009 2:08:50 pm BST

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?

   
Reply to vid

6

 Raj, on Nov 20, 2009 1:05:03 am GMT

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

   
Reply to Raj
Ask your question Reply
They need your help