146

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

3 Answers 3

Reset to default
43

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
3
  • 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, 2020 at 11:06
  • This doesn't seem like it will work very well if your paste buffer is very large
    – Michael
    May 12, 2021 at 22:25
  • @IanGrainger the user that submits the questions chooses the accepted answer; the better answer came along about two years after the user accepted this answer.
    – troyfolger
    Jan 21, 2022 at 17:57
242

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

screenshot

Improve this answer
7
  • 7
    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, 2018 at 9:18
  • 1
    I was going to write the same comment as @rob, thank you!
    – Graph
    Jan 5, 2019 at 21:17
  • 20
    This should be the accepted answer.
    – Clive Portman
    Jan 18, 2019 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, 2021 at 20:48
  • 1
    Doesn't work for me. The "Split text to columns" option is greyed out.
    – Dennis
    Jul 21, 2021 at 19:01
67

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
3
  • 2
    That worked in Calc (libreoffice). The Data menu has a "Text to Columns..." option and it worked just as intended.
    – Alexis Wilke
    Sep 30, 2019 at 23:03
  • 2
    Thanks for this! The other answer that uses the little icon doesn't work for pasting multiple rows (the icon doesn't show up). This way is much more consistent!
    – rococo
    Nov 27, 2021 at 7:55
  • Thanks, this worked for me as well.
    – Varun Sharma
    Mar 9 at 3:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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