Ask Your Question

I want to redistribute the contents of one column into several [closed]

asked 2017-11-06 03:33:12 +0100

Kaelkitty gravatar image

updated 2020-08-13 16:49:09 +0100

Alex Kemp gravatar image

I have one column of data, of the form -

Original Data

  • [Icon0001]
    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.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-13 16:49:29.771496


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++.

Mike Kaganski gravatar imageMike Kaganski ( 2017-11-06 08:17:10 +0100 )edit

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 gravatar imageKaelkitty ( 2017-11-06 22:04:01 +0100 )edit

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

Kaelkitty gravatar imageKaelkitty ( 2017-11-06 22:30:17 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-11-06 05:04:34 +0100

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

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2017-11-06 03:33:12 +0100

Seen: 57 times

Last updated: Nov 06 '17