[Solved] Opposite to Concatenate

Discuss the spreadsheet application

[Solved] Opposite to Concatenate

Postby quarkrad » Fri Oct 06, 2017 9:42 am

I am looking for a function(s) that will split a string (in a cell) into multiple columns (and I would like to choose what columns they go in). In A1 there is a string of 4 sets of data separated by a space. E.g. 123 abc 5/Oct/2017 xxxx. What I would like to do is split A1=123 abc 5/Oct/2017 xxxx into A5=123 A6=abc A7=5/Oct/2017 A8=xxxx. Also, what would be the difference is the string was separated by a comma rather than a space. Many thanks for any help on this one.
Last edited by Hagar Delest on Sun Oct 08, 2017 5:58 pm, edited 1 time in total.
Reason: tagged [Solved].
libreoffice 5.4.1.2 on ubuntu 16.04
quarkrad
 
Posts: 43
Joined: Mon Nov 03, 2008 10:01 am

Re: Opposite to Concatenate

Postby RoryOF » Fri Oct 06, 2017 9:46 am

Look up Help on "Text to Columns"
Apache OpenOffice 4.1.4 on Xubuntu 16.04.03 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 25515
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Opposite to Concatenate

Postby quarkrad » Fri Oct 06, 2017 11:40 am

Thank you. I want to do this automatically, so would like to have a function in a cell(s) that would carry this out rather than have to manually evoke Date/Text to Columns
libreoffice 5.4.1.2 on ubuntu 16.04
quarkrad
 
Posts: 43
Joined: Mon Nov 03, 2008 10:01 am

Re: Opposite to Concatenate

Postby robleyd » Fri Oct 06, 2017 12:11 pm

It seems that you are getting this data in combined form? Can you explain how it is received and added to your spreadsheet - there may be a solution at that level.
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.4 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1091
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Opposite to Concatenate

Postby quarkrad » Fri Oct 06, 2017 12:33 pm

To overcome a problem with a macro (using multiple cells) I concatenated them into a single cell. I can now use the macro to copy/paste to another spreadsheet and so have to split the string into the original cells. I found a solution that works using =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999)).
libreoffice 5.4.1.2 on ubuntu 16.04
quarkrad
 
Posts: 43
Joined: Mon Nov 03, 2008 10:01 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 29 guests