I want to redistribute the contents of one column into several

[Kaelkitty]

I have one column of data, of the form -

Original Data

• [Icon0001]
  Legacy=0
  XCoord=14
  YCoord=118
  Name=Dell
  Path=C:\Users\Dell
  Monitor=0
  [Icon0002]
  Legacy=0
  XCoord=14
  YCoord=2
  Name=Computer
  Path=::{20D04FE0-3AEA-1069-A2D8-08002B30309D}
  Monitor=0
  etc.

I want to make it into 6 columns of the form

Icon Number	Status	X=		Y=			Name			Path						Monitor

[Icon0001] Legacy=0 XCoord=14 YCoord=118 Name=Dell Path=C:\Users\Dell Monitor=0;

[Icon0002] Legacy=0 XCoord=14 YCoord=2 Name=Computer Path=::{20D04FE0… …09D} Monitor=0

The original data is a long text file with each datum on one line, so L O Calc is importing it as a single column. Can anyone tell me how to fix this. I don’t know whether I should be doing something different when I import the data, or if I can use some combination of formulae and filtering. Please help as I am fairly inexperienced with spreadsheets and this is giving me a headache!

Ciao,
Kaelkitty.

From your answer

“You either have to preprocess your data in a text editor to convert each newline not preceeded by x into some other delimiter, or create a macro.”

Given that this is a LONG file (about 400 items or 2400 lines in it’s present form) I don’t really fancy doing that many cut and paste operations, preprocessing would seem to be a no-go. I’ve never written a macro in Calc so I’d appreciate some help in knowing what to do.

I also tried copying and rewriting the formula {=T(OFFSET($A$1;ROW()*4+0;0))} that JOHNSUN mentioned in the comment on your answer but I can’t seem to get the changes that would work on my sheet.

[mikekaganski]

Preprocessing isn’t necessarily “many cut and paste operations”. In many plain-text editors, you can simply use regular expressions to do the preprocessing easy. E.g., a search regex ^(\[[^\n]*\])\s*\n([^\n]*)\n([^\n]*)\n([^\n]*)\n([^\n]*)\n([^\n]*)\n([^\n]*) and replacement string $1\t$2\t$3\t$4\t$5\t$6\t$7 would work for me with Notepad++.

[Kaelkitty]

Hmmm, I’ve got Notepad, Wordpad and LibreOffice Writer on my system - no Notepad++. Notepad is the only one I really cope well with (I’ve got disability issues with my memory and find the others very complex to use). I just want to get this data into readable form!

[Kaelkitty]

I’ve been mulling over potential formulae, and have come up with something I could use to auto-fill the information in the pattern I want, but I still have a problem. I want to use a pattern like this -
=IF(A20<>“begins with [Icon”," ",A20) except I can’t work out how to replace “begins with [Icon” with an actual comparison that works

[mikekaganski]

importing text files with newline delimiters

I’d like to create a spreadsheet from a file that looks like this: [Text File] When I open the text file I see an option for OTHER delimiter, but don’t know what to type in there to say NEW LINE or ENTER or whatever the term is that the import dialog uses. I want the spreadsheet to look like this: [spread sheet] Thanks, wp Yes, and I am really really really really really not a robot… sigh btw, this is what the doc says for the OTHER file. NOT very helpful, or are Non-Printing characters a…

See @JohnSUN’s comment. Or preprocess it previously to importing.