• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

I HAVE AN EXCEL FILE WHERE ALL OF THE DATA IS IN 1 ROW - HOW DO I CHANGE IT

I HAVE AN EXCEL FILE WHERE ALL OF THE INFO IS IN 1 ROW HOW DO i PUT IT INTO MULTIPLE ROWS?
0
DOLLARDOLLAR
Asked:
DOLLARDOLLAR
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
SimonCommented:
Please post a sample workbook.
0
 
DOLLARDOLLARAuthor Commented:
I don't see how to put a file in?
0
 
DOLLARDOLLARAuthor Commented:
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
DOLLARDOLLARAuthor Commented:
I downloaded the file
0
 
Danny ChildIT ManagerCommented:
OK, so your problem is that cell B2 (merged into C2) contains a really long text string, split by Alt+Enter line breaks, and that you want to make it into proper multiple rows...
0
 
Danny ChildIT ManagerCommented:
OK, this is close, but I don't have a lot of time to make it pretty...

On another cell, say K16, put in this formula:
=SUBSTITUTE(B16,CHAR(10),";")
This basically searches B16 for the hidden ASCII character 10, and replaces it with a semi-colon;

Then, copy this cell, and Paste Special.. Values one cell to the right - L16

Then, use Text to Columns on here, on the Delimited option, and use ; as the delimiter.

It produces multiple cells, one per line of the original (with the exception of the first 2 lines, which don't seem to have this separator.

You could then use Paste Special.. Transpose, to turn this horizontal row into a vertical column.
ee-RemoveAltEnters.xlsx
0
 
Danny ChildIT ManagerCommented:
Hmmm, not quite perfect, a few other cells didn't split.  Only other option would be to use a macro to force the split whenever it sees 4 numbers together.  Anyway, gotta go...
0
 
SimonCommented:
Here's another approach...
1. Set the constant myOffset to the number of columns to the right to create the multi-line table. Make sure this is to the right of your used range on the sheet.
2. select each cell in the table and run this macro on each of them in turn to create single line entries.
Sub SplitCellToRows()
Const myOffset = 12 'row offset to write the multiple line results to.
Debug.Print ActiveCell.Value
x = Split(ActiveCell.Value, Chr(13))
For y = 0 To UBound(x)
Debug.Print x(y)
ActiveCell.Offset(y, myOffset).Value = x(y)
Next
End SubÃ

Open in new window

0
 
DOLLARDOLLARAuthor Commented:
I received another file from my supplier...thanks for the suggestions
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now