I am trying to do the Text to clumns command for columns where the cells look like this:

10000010011> 10101010101>01100111000> 101101010110

> is used to split the data into 4 columns.

Some data like for example this 000011001110 changes (all the first zeros are cut) turning it into this 11001110.

Formatting as a text makes some data (011111110111000) look like this: 1,11111E+13.

Does anyone have a idea? I tried a lot of different formats.

I don't want to put a apostrophe in front of every input.

  • @MátéJuhász that would be same as adding ' before every number. – Sandeep May 10 '18 at 8:39
  • @Sandeep you should try this out for yourself before making a comment – PeterH May 10 '18 at 8:42
  • I tried this but the problem is I have to do the command Text to Columns and then formatting as a text makes some data (011111110111000) look like this: 1,11111E+13. – Ads5 May 10 '18 at 8:44
  • 1
    @Sandeep: "that would be same as adding ' before every number." - output might be similar yes, however if you need to enter many numbers, than it's a big difference whether you need to type 100 entries manually adding ' OR you just set all number formats at once than paste all the values at the same time – Máté Juhász May 10 '18 at 8:54
  • 1
    @MátéJuhász Thanks that was the solution! Selecting all. I didn't know that that was for selecting specific colums :) – Ads5 May 10 '18 at 9:10

Setting data type during "text to columns" works for me:

enter image description here

Note: You can set data column differently for columns, make sure to select all and set "text" for all of them.

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.