108

Say I have a string of: l,f,x,a,s,f

I would want to paste this every comma-separated value over 6 adjacent cells. How would I do it?

Improve this question
31

You can paste it into a split function in a cell and it will break it apart into multiple cells for you:

=SPLIT("l,f,x,a,s,f", ",")
Improve this answer
2
  • 1
    This doesn't seem like the answer to the question. I would've expected either of the ones with more points to be the accepted answer. – Ian Grainger Dec 15 '20 at 11:06
  • This doesn't seem like it will work very well if your paste buffer is very large – Michael May 12 at 22:25
181

From https://support.google.com/docs/answer/6325535?hl=en :

  • Open a spreadsheet in Google Sheets.
  • Paste the data you want to split into columns.
  • In the bottom right corner of your data, click the Paste icon.
  • Click Split text to columns. Your data will split into different columns.
  • To change the delimiter, in the separator box, click

PS: There is no option for 'TAB' if your text is tab-separated, you can try 4 spaces as custom separator

Improve this answer
4
  • 5
    After pasting in the CSV data I get no Paste icon. But in it's place is a tiny square that acts as a "handle" for resizing. Because of your answer I tried right clicking it and low and behold the Paste options then appear and I can choose Split. Thanks – rob May 21 '18 at 9:18
  • 1
    I was going to write the same comment as @rob, thank you! – Graph Jan 5 '19 at 21:17
  • 14
    This should be the accepted answer. – Clive Portman Jan 18 '19 at 9:27
  • Got a little tripped up pasting into an existing sheet because I was trying to paste into a frozen column, or one where adjacent columns were reserved / uneditable. Just make sure you have a big open space with no restrictions when pasting – dwanderson May 10 at 20:48
45

You are able to paste CSV data as is into the spreadsheet and go to Data selecting Split text to columns... and even further specifying the delimiter:

0

0

Also, you can simply use keyboard shortcut combo:

LEFT ALT + D + E

And as already mentioned SPLIT formula:

=SPLIT("l,f,x,a,s,f"; ",")
=SPLIT(A1; ",")

For a range/array it would be: 

=ARRAYFORMULA(IFERROR(SPLIT(A1:A; ",")))

0

Improve this answer
1
  • 1
    That worked in Calc (libreoffice). The Data menu has a "Text to Columns..." option and it worked just as intended. – Alexis Wilke Sep 30 '19 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.