3

You know when you copy a row from a table and paste it into another one it is in the correct place. So what is the character that is between cell values when this operation is performed.

Eg. I have this text: Phone (P), Email (E), Home (H) and if I replace all the , with \n and paste the text in a tabular formant in Pages/Word/Excel, it is interpreted as a column.

To replace the string with new line I used:

> pbpaste | tr -s ', ' '\n' | pbcopy

I tried \t, and \n\n but they're still pasted as a row.

Next I tried copying it in a row and printing on the terminal using (gcat because I'm on macOS):

> pbpaste | gcat -A 
a^Ib^Ic⏎

Reading the docs it says:

display TAB characters as ^I

But replacing , with \t doesn't do it. Does anyone know what character this is and how I can replace a comma with it to paste a row?

Improve this question
9
  • It’s the tab character
    – Tony Stewart EE75
    Jun 26, 2018 at 21:55
  • If \t doesn't do it, maybe ^t will?
    – cybernetic.nomad
    Jun 26, 2018 at 21:56
  • 1
    tr is the wrong command for this task. Try pbpaste | sed 's/, / /g' | pbcopy. Note that you can't simply copy the command from this comment. Control-v followed by the tab key allows you to enter a tab character on the Terminal command line. When typing this command in Terminal, type s/, /control-v tab/g.
    – creidhne
    Jun 27, 2018 at 0:29
  • @creidhne funny thing is when I asked the best way to replace a substring with another string before people said to not use sed and use tr.... sed is quite confusing for me and for a simple replace without any regex it's not anymore helpful.
    – user14492
    Jun 28, 2018 at 13:11
  • 1
    But did the sed command solve the problem? The problem with tr is that it translates characters, not strings. sed uses so-called "basic regex" but sed -E uses modern "extended regex". The sed command replaces every , (comma followed by a blank) with a tab character. Would an answer help?
    – creidhne
    Jun 28, 2018 at 13:49

1 Answer 1

Reset to default
0

In Excel, you can define which character is used as a delimiter by using the "Text to Column" option.(I discovered this by going crazy when I was pasting values and it was getting split somehow.)

If you go to the Text To Column option in the Data tab, it first asks if it is Delimited or Fixed Width. Select delimited, then hit next.

On the next screen, you may select Tab, Semicolon, Comma, Space or Other (with a field to say which is the other character). Set it there and finish.

If you had, for example, 1,2 and pasted normally, it would fill only one cell. However if you do this process and select Comma, when you paste again it will split on the Comma.

It is not exactly what the question asked for, because the asker wanted to know what to replace the comma and in various programs, but this way you do not need to replace it at all, instead making Excel understand you want to split on the comma.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .