UK sort code and account number problem

[sunshine1325]

Hi
Thanks for any help in advance.

In the UK banks have sort code and account number...

Sort code is 6 digits long for example 001122

Account number is 8 digits long say 01234567

My question is that if the first digit s a zero then sometimes in get chopped of. Is there a formula I can use so that it doesn't remove this leading "0"


If not possible then how could I add say "SC00000" into the cell but just by adding "00000". the SC would be added from a formula.

Thanks

[AliGW]

Set custom formatting for the cells containing the number - type 00000000 into the custom formatting dialog.

Excel 2016 (Windows) 32 bit

	A
1	01234567

Sheet: Sheet1

Use 000000 for the sort codes.

[sunshine1325]

Thanks for the reply.

On my excel that still removes the leading zero if it says is 0123456

returns 123456

[AliGW]

You have used the wrong formatting for the A/C number!

8 zeroes for account numbers; 6 zeroes for sort codes.

You shouldn't be dealing with any 7-digit numbers - that's not what you asked for!

[Richard Buttrey]

Just format the cells as text

[sunshine1325]

Thanks Ali thats now works

[SDCh]

You can use formula :

Please Login or Register  to view this content.

if you want add "SC" :

Please Login or Register  to view this content.

Note:
you can add "0" for how long the number you need


or if you prefer to use custom formatting (excel 2019 for windows):

Please Login or Register  to view this content.

[AliGW]

No problem.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.