Excel 2010

[andyGTBristol]

Hi,

Can anyone help me out with formulaes on Excel ?

I have a spreadsheet with account number that are in various conatations, they are all 16 digits and I want them all to be 4 digits then a space and the remaining 12.

Is there any easy way ?

[sfby]

Assuming your original account number is in text format in cell a1..

=LEFT(A1,4)&" "&RIGHT(A1,12)

[dr b]

sfby's formula will work even if the original account number is formatted as a number rather than text.

[andyGTBristol]

Thanks guys, will formula work even if there are various gaps in the account numbers ie 4 and a space, another 4 and a space etc ?

[dr b]

no, the LEFT formula takes only the leftmost 4 characters even if some are blanks. I think the easiest thing to do would be to select the range containing the account numbers and replace " " with "" (i.e. a blank with a null character). Then apply sfby's formula.

[verndoug]

I assume that you want to show the 16 digit number 1234567890123456 to appear as 1234 5678 9012 3456 (to appear with spaces but not to have to place a space).

Format the cell/cells/rows/columns that require this by clicking:
Format, then Number, then Custom and type in 0000 0000 0000 0000.
Then those selected cells will be formatted to your wishes.
If you want 1234 567890123456 then format as 0000 000000000000