How to I add space automatically to a 22-digit number?
For example: 1234567891234567891234 needs to be formatted to 123456 7891234567891234 (basically added a space after 6).
5 Answers
Excel only allows up to 15 significant digits so a 22-digit number can only be represented in Excel as a text value, hence you can't use number formatting. To get the required result in another cell you can use REPLACE function to add a space at character 7, i.e.
=REPLACE(A1,7,0," ")
Use the "Left", "Right" and "Concatenate" Excel string functions.
If A12 = 1234567891234567891234 then use something like:
=CONCATENATE(LEFT(A12,6) & " " & RIGHT(A12,16))Take note of the first response that stipulated that the cell containing the 22 digit number be formatted as a string (assuming that you are inputing or importing it without the desired space).
If you are importing, I would suggest that you prepend a single-quote character in front of the value imported, to ensure that Excel interprets it as a string. I would also suggest a VBA routine that would change the 'no-space' version into the 'space' version something like this, that you would run on the specified cells after the data has been entered / imported...
Sub AddSpace()
Dim cl As Range
For Each cl In Selection If Mid(cl.Value, 6, 1) <> " " Then cl.Value = Left(cl.Value, 6) & " " & Right(cl.Value, Len(cl.Value) - 6) End If
Next cl
End SubThis tests that you aren't inserting a double-space, and will treat shorter values as well. You could also test for all-numeric values, etc.
Cheers,
Dean
nothing ...after selecting required cells just press ctrl+F and opt replace option and edit in "find what" as the normal text and numbers and add in "replace with" in what model you want cintends (ie.text with space) .then press replace all.
Simple formula is =TEXT(A1,"0000 0000 0000")
1
