asked on

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

Ryan Chong

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

Nirvana

ASKER

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

Kimputer

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

Ejgil Hedegaard

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.

Nirvana

ASKER

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

Kimputer

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.

Nirvana

ASKER

the file has many rows (minimums of 500 rows) Kimputer as these are bank details i am afraid i cannot share the details.

Kimputer

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)

Nirvana

ASKER

Nirvana

ASKER

attached is the full file

Kimputer

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.

Nirvana

ASKER

Sure thanks a lot Kimputer

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nirvana

ASKER

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.

Nirvana

ASKER

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