Excel custom cell formatting: formatting code for alphanumerical entry with spaces?
Arctiic
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!
Ron Rosenfeld
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
Related Related
- 1
Conditional Formatting with Custom Formula referencing the cell itself
- 2
excel assign the value and formatting of a cell
- 3
Formatting in cell depending on row in Excel
- 4
(Excel) Conditional Formatting based on Adjacent Cell Value
- 5
Clear contents and formatting of an Excel cell with a single command
- 6
Custom Formatting dimensions of a box in Excel
- 7
Excel: copy cell formatting in equation
- 8
In Excel, how do you do custom number formatting so that it has leading blank spaces?
- 9
Excel: Custom number formatting
- 10
Excel 2010 Cell Formatting
- 11
Excel conditional formatting only applies to first cell
- 12
Conditional Formatting in Excel with Formula Current Cell
- 13
google sheets: custom formatting basing on cell below
- 14
VBA Excel Cell formatting on specific days on open
- 15
Formatting in cell depending on row in Excel
- 16
Excel conditional formatting based on cell content
- 17
Excel custom function | formatting
- 18
Excel Conditional Formatting based on Previous Cell
- 19
Cell Formatting, Conditional Formatting, or Formula
- 20
Excel conditional formatting with multiple cell comparison
- 21
Clear contents and formatting of an Excel cell with a single command
- 22
Excel: Different formatting on different values in same cell
- 23
Excel IF statement conditional cell formatting
- 24
Conditional formatting using cell styles in Excel 2007?
- 25
Excel Conditional Formatting (Range vs single cell)
- 26
F# code formatting: adjust spaces
- 27
Excel cell color formatting
- 28
Excel VBA copy cell formatting to range
- 29
Excel Formatting Numbers in Cell
Comments