1

In my country any bank account number has a secondary key which named SHEBA, and there is formula to calculate the SHEBA from account number.

For example if my account number is 801-800-125954-1, the SHEBA of this number is IR0008010080000125954001.

As you can see, changing the account number to SHEBA has been done by putting a handful of zero between account number's digits (however It's not always so simple).

So, I want write formula in Excel that can put zero - or any other digit - between our Number of customer accounts.
I mean, write function in which it's input is a number and output is same number plus some another digit between number

VonC
  • 1,262,500
  • 529
  • 4,410
  • 5,250
user3692859
  • 21
  • 4

2 Answers2

0

If you can split your string after each '-', you can pad each one with the appropriatz number of '0'.
Se "Add leading zeroes/0's to existing Excel values to certain length" (using TEXT)

801 would become "IR"+TEXT("800","000000")

http://jasonwhurley.files.wordpress.com/2012/02/padnumbertoleft.jpg?w=595

Community
  • 1
  • 1
VonC
  • 1,262,500
  • 529
  • 4,410
  • 5,250
0

Since you only posted one number and one SHEBA, it is a bit hard to establish a pattern, especially if, as you say "It's not always so simple".

You can use one formula to get from your number to your SHEBA

="IR000"&SUBSTITUTE(A1,"-","00")

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73