Certain way of displaying info in cells

[ishirra]

Hello,

I work with ocean containers and they all have an ID. The ID exists out of 4 letters and 7 numbers.
When I copy paste a container number into a cell it is like ABCD123467. I want it to be displayed like this: ABCD 123 456-7
Is there a way to do this, so I don't have to put the spaces in it myself?

Thank you in advance

[José Augusto]

Hi
Try this approach
If your data is in A1 use this formula
=LEFT(A1,4) & TEXT(VALUE(RIGHT(A1,7))," #### ###-#")

[TudyBTH]

Or this

Please Login or Register  to view this content.

if you have US Regional seting change ";" with ","

[ishirra]

The data will be copy pasted into the same cell as to where the formula will be.

I copy the text from another program, and need to put it in excel.
In the other program it is put as ABCD1234567. In excel (to make it easier to read) I'd like to have it show up as ABCD 123 456-7.
So if I copy paste the date into cell A1, I want cell A1 to show it as ABCD 123 456-7.

[JohnTopley]

You can't do that: the formulae offered are the only options you have.

[TudyBTH]

or you can fix this with a macro while importing data (not copy-paste)