How can I format multo row text format to excel to columns

How do i format multi level row text into excel columns

Dokumentnr.      Bilagsnr. Status        Leverandørnr. Leverandørnavn      Leverandørsted    - kontonr.           Dokumentbeløp Bet
------------ ------------- ------------- ------------- ------------------- ---------------- ---------------- ------------------ ---
Sekv.                             Fakturanummer THATKURA-
   nr KID nummer                                dato      Beskrivelse         Bruttobeløp        Rabattbeløp     Betalingsbeløp Bet
----- --------------------------- ------------- --------- ------------ ------------------ ------------------ ------------------ ---
                       Deklarasjonskode            Dokumentsekvensnavn               Fakt. Bilagsnr.       Forfallsdat Betalingsdat
                       ------------------------    --------------------------------- --------------------- ----------- ------------
     7777777                             999999999      LITE MARK A/S      IDTO.            11111111111                 875.00  Ja
   10 2115165572226389            229737        27-APR-15                         875.00               0.00             875.00
                                                   2401A                             3355148               12-MAI-2015 12-MAI-201
NirvanamanagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
Depending on your requirements and I assume there is some proper formatting in your source file, you may try use the Load from Text wizard available in Excel or try the Data Tools > Text to Columns

Import data using the Text Import Wizard
https://support.office.com/en-ca/article/Import-data-using-the-Text-Import-Wizard-40c6d5e6-41b0-4575-a54e-967bbe63a048

Text to Columns
http://www.excel-easy.com/examples/text-to-columns.html
0
NirvanamanagerAuthor Commented:
Hi Ryan Chong Thank you for the quick reply. I have tried text to column delimiter, however it will not work because as you can see in the sample the data is in multiple rows and the data fro each column is different.

Thank you
0
KimputerCommented:
Wow that text looks ugly. Can you tell with words, graphics, text files, or something else how to fix this piece of text? Maybe then we can translate it to excel columns, but YOU need to explain first.
Example: Line 3 should be pasted behind line 1, line 7 8 9 should be combined. Etc etc
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ejgil HedegaardCommented:
It looks like a print from an ERP system with 3 header lines and 3 data rows for each record.
Print like that are typically precisely formatted, so a given number of characters are used for each field, and can be split using text to columns with fixed width.
Insert the text in excel, and change the font to Courier, and you will see that the data rows align with the headers.

The data must be put into 3 columns before split, and for each column the text to column performed.
Some of the headers are in 2 rows, and need to be manually set.
It is not difficult, but require more data rows to have enough information to make the split.
0
NirvanamanagerAuthor Commented:
Hi Kimputer/ Ejgil,

sorry for the late reply below (attachment ) the format that i am looking for in excel. I have tried with courier, however i have about 10000 rows that needs to be formatted which is a challenge
124-payment.xlsx
0
KimputerCommented:
The first file you posted, the text, is it always one row? Or you have more examples where there are more rows? Please post the file, not copy&paste as text here.
The reason is, otherwise we'll try to fix that one line, and later find out there could be files with even more lines.
0
NirvanamanagerAuthor Commented:
the file has many rows (minimums of 500 rows) Kimputer as these are bank details i am afraid i cannot share the details.
0
KimputerCommented:
Try to shorten the file to about 5 rows, then edit the sensitive information (but if it's 9 numbers, replace with fake 9 numbers, same for text, if it was a string of 10 chars, overwrite with exactly 10 chars)
0
NirvanamanagerAuthor Commented:
0
NirvanamanagerAuthor Commented:
attached is the full file
0
KimputerCommented:
Thanks for the full file. As now it's obvious the header comes back every once in a while.
Will take some time to find a solution, so please hold on.
0
NirvanamanagerAuthor Commented:
Sure thanks a lot Kimputer
0
Ejgil HedegaardCommented:
Here is a way to do it manually.
On Sheet2 is the import of the data, and in column B a formula to determine which rows has data.
The numbers refer to the data lines 1, 2 and 3, and is found using something that is unique for that record type.
=IF(LEN(A2)<120,0,IF(ISNUMBER(VALUE(MID(A2,95,5))),1,IF(ISNUMBER(VALUE(MID(A2,4,2))),2,IF(ISNUMBER(VALUE(MID(A2,91,2))),3,0))))

Open in new window


Filter on column B and use all rows that is not 0.
Copy the rows including column B to Sheet3, starting at A3.
Negative values are enclosed in <>, so use search for "<" and replace with "-", and search for ">" and replace with " ", to ensure that text to columns does it correctly.

The formulas in column D, E and F on Sheet 3 align the records.
I have added the headers from the initial post here.
Record 1 is missing for some, so it is repeated.

Filter on column E for not empty.
Copy the result in column D to Sheet4, A1.
Use text to columns, using fixed width.
The split must be done at the end of each ----- in line 2.
Set the format to Text or Date when needed.
KID nummer has leading zeros, so I would use Text on that column to keep that.

Copy Sheet3, column E to Sheet4, K1 and make text to columns on that.
Copy Sheet3, column F to Sheet4, T1 and make text to columns on that.
Finally delete the empty columns, and row 2.

In the file I have only transferred Sheet3, column D to Sheet4.

The above description looks more difficult than it is when you do it.
The process from Sheet3 to Sheet4 can be done with formulas, using the ------- to determine start and end point for each cell, but every column has to be written separately to remove leading spaces, get the result as text, values or dates, so if it is not done very often I would use the capability of the text to column method to translate.
124-payment.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NirvanamanagerAuthor Commented:
Ejgil Hedegaard you are genius. Cannot thank you enough for the solution. it might be little difficult when i do it for the new files but surely will work around.

Thank you again Guru.
0
NirvanamanagerAuthor Commented:
Data taken from ERP coming as multiple rows with no specific format to do text to column delimiter.  Ejgil Hedegaard, has provided a solution and demonstrated the solution brilliantly
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.