Speadsheets

[andyGTBristol]

I have a spread sheet with account numbers (all 16 digits) they have a space every 4, is there an easy formuala I can use to remove spaces and just leave one space after the first four ie 1234 123412341234

The spreadsheet has approx 5000 accounts on

Thanks

[dr b]

why was this not answered adequately here?

http://www.theanswerbank.co.uk/Technology/Question1265359.html

use the "replace" function (hit control-H) to replace the " "'s with "". Then use the formula in your other post.

[dr b]

or if you don't like the replace feature:

if your numbers are: 1234 1234 1234 1234

=left(a1,4)&" "&mid(a1,6,4)&mid(a1,11,4)&mid(a1,16,4)

assuming your acct number is in a1.

[andyGTBristol]

I tried that it and it screwed up the account number, missing a couple of digits

[dr b]

I can't see how replacing blanks would leave out any digits. What form exactly are your account numbers?

[Aquariel]

As above, or if you want to do it with one formula, you can use something like this:

=CONCATENATE(MID(B4,1,5),MID(B4,6,4),MID(B4,11,4),MID(B4,16,4))

Then you can just copy the formula results back over the original cells - use Paste Special and select Values. Et voila!

[Aquariel]

Just seen Dr. B's second reply after posting - at least we're thinking the same way! It does work, Andy. I've just tried it on an example number.

If it doesn't work for you, maybe you actually have a custom number format on those cells, and they are just entered as pure 16-digit numbers. The custom number format of "#### #### #### ####" will display them with spaces as per your source data, and the formulas we've given will miss out digits as you suggest it seems to do. Reset the format of the original cells to General to see if that changes how they look.

[andyGTBristol]

Just tried again and it works now (my fault first time)