3

I've pasted this json into a cell in excel

{
    "Name": [
        "OldName",
        "NewName"
    ],
    "Address[Id: 1].Postcode": [
        "AL10 8DN",
        "GU1 1ET"
    ]
}

But it appears like this:

enter image description here

If I click inside that cell, it formats itself properly again:

enter image description here

Is there a way of getting it to stay formatted, whether the cell is selected or not?

Improve this question
1
  • on notepad++, I had to convert json TAB to SPACE and Paste in excel Cell with F2 did trick for me
    – PKV
    Mar 16 at 21:31

2 Answers 2

Reset to default
4

Strangely enough, I don't seem to have this issue. Using Office 365.

If I copy and paste your first "code formatted" JSON text section, and paste into Excel (select single cell, but not edit, hit CTRL+V) then in pastes it into a single column across 10 rows. The font changes to "Var(--ff-mono)". The spacing remains intact.

enter image description here

If I do the same, but edit the cell before pasting (i.e. select cell, double-click/press F2, CTRL+V), it pastes everything into a single cell. "Wrap text" comes on automatically, and the row height jumps up. After resize both the column width and the row height, it looks like this (font stays the default Calibri):

enter image description here

TL;DR Paste your json data into notepad or possibly superuser first (to rid your data of all formatting, then select all and copy again, paste into Excel.

If that doesn't work, you could try reapplying / pre-applying (paste with destination formatting) either the calibri or your default font, or a monospace font like Courier New. Last resort, try Office 365.

Improve this answer
3
  • 1
    Thanks Mobus, even just removing the tabs and manually adding spaces in the cell worked - I guess excel doesn't like whatever tab character was coming out of notepad++
    – Bassie
    Mar 8, 2022 at 11:21
  • Pasting into your browser probably swapped out the tab for a space. Your character encoding/regional language or keyboard settings in Windows / your OS may also be interfering, particularly with non-english setups. Excel can be very tricky when importing data, as it uses the regional settings of the OS and not of the (unspecified) source in the clipboard or CSV file. E.g. importing a CSV file with "," as decimal separator will fail horribly if windows regional settings specify "." as the decimal separator. Similar things could be a foot with the tab character and current unicode scheme
    – ExcelEverything
    Mar 8, 2022 at 11:30
  • 1
    @Mobus Browsers don't strip out tabs for spaces. StackExchange does when presenting the post. If you press Edit on the main post you see it with the actual tab characters
    – Ferrybig
    Mar 8, 2022 at 19:01
1

I don't know how you pasted the text inside the cell, but new-lines inside text are not conditional on the cell being the current. In fact, when pasting text with new-lines, each line goes into a separate row, so not into one cell.

If you would like the text to be properly formatted with new-lines, place your cursor in the position for the new-line and type Alt+Enter for the new-line.

It works like this:

enter image description here

Improve this answer
1
  • 1
    The issue doesn't seem to be with new lines, but rather with tabs. I copy-pasted that json from a notepad++ file, perhaps it is due to the encoding? pressing alt+enter inside the cell doesn't do much, as the new lines are already present (event when its not selected)
    – Bassie
    Mar 8, 2022 at 11:19

You must log in to answer this question.

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