I'd like to apply a custom cell formatting that takes an alphanumerical entry and outputs with predetermined visual spacing (no spaces in actual data). The purely numerical equivalent of what I'm trying to do would have a formatting code like this:
0000 0000 0000 0000
However, I'd like to have this apply to both text string as well as integers, but can't seem to figure out what the correct formatting code and syntax is?

The official help documentation states @ for text string, but this seems to disregard the number of characters from what I can tell, as @@@@ @@@@... seems to reprint the entire entry per-@. Hope to get some clarification on this, thanks!

  • 1
    That is not a feature of excel cell formatting. You will need to use either a formula in a separate cell, or a VBA routine. And the result would be a string with the format (i.e. the spaces) and not the original string that is merely displayed with the format (as is the case with numbers). – Ron Rosenfeld Oct 29 '19 at 18:51
  • @Ron Rosenfeld - That looks like an answer to me. – cybernetic.nomad Oct 29 '19 at 19:02
  • @cybernetic.nomad Perhaps. I'll post it as such, using the formula method since that would retain the original data. – Ron Rosenfeld Oct 29 '19 at 19:20

That facility is not available in Excel.

You could add a column with a formula, which would display the original string in the format you specify, and still retain the original data for future use.

eg: the array formula

=TEXTJOIN(" ",TRUE,MID(SUBSTITUTE(A1," ",""),(ROW(INDIRECT("1:"& ROUNDUP(LEN(A1)/4,0)))-1)*4+1,4))

Since this is an array formula, you need to "confirm" it by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar

enter image description here

| improve this answer | |

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.