Page 1 of 1
[Solved] Opposite to Concatenate
Posted: Fri Oct 06, 2017 9:42 am
by quarkrad
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.
Re: Opposite to Concatenate
Posted: Fri Oct 06, 2017 9:46 am
by RoryOF
Look up Help on "Text to Columns"
Re: Opposite to Concatenate
Posted: Fri Oct 06, 2017 11:40 am
by quarkrad
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
Re: Opposite to Concatenate
Posted: Fri Oct 06, 2017 12:11 pm
by robleyd
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.
Re: Opposite to Concatenate
Posted: Fri Oct 06, 2017 12:33 pm
by quarkrad
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)).