3

I have a system which originally got account numbers from legacy system whose account numbers used zeros as a prefix. When the account numbers were brought into Salesforce the zeros were trimmed off and they do not exist in the Salesforce account ID.

Now we are being forced to refer to the old system for various reasons. So I need to figure out a way to match the current Salesforce account IDs with the original ones with the zeros.

If it were a matter of adding a specific amount of zeroes to the prefix of the Salesforce account IDs I wouldn't be sweating it. But some of the old accounts filled their zeros. So I have account numbers of an inconsistent type such as :

61260012418
60008030650
60015107804

Essentially, I want to trim the leading 6 and all of the zeros which immediately follow it. So my final numbers should be as such : 

126001241
8030650
15107804

What would be the best approach for this ? If I could figure out a field formula to trim them then I could just import the entire IDs into Salesforce and trim off everything from the final 0 to the left. Does anybody know of any logic which could do that ?

If you knew of an Excel formula which could do it before I import it into Salesforce that would work too.

Improve this question
1
  • 61260012418 for this what should be the output?
    – Avijit Das
    Apr 27, 2017 at 13:40

3 Answers 3

Reset to default
7

Here it is, most simple way to achieve this

IF(
   BEGINS(Customer_Account_No__c, "6"),  
   TEXT(VALUE(RIGHT(Customer_Account_No__c, LEN(Customer_Account_No__c)-1))), 
  "")

Converting Text to Value() function trims off leading zeros. This is the trick!!!

Or, with the use of MID rather than RIGHT function

IF(
   BEGINS(Customer_Account_No__c, "6"),  
   TEXT(VALUE(MID(Customer_Account_No__c, 2, LEN(Customer_Account_No__c)-1))), 
   "")
Improve this answer
5
  • I think MID might still be a bit more efficient but that's certainly a huge improvement on my approach!
    – Adrian Larson ♦
    Apr 27, 2017 at 14:05
  • Yes, I can change RIGHT to MID
    – Santanu Boral
    Apr 27, 2017 at 14:06
  • I'm glad my way was not the best one. That would not have been very scalable.
    – Adrian Larson ♦
    Apr 27, 2017 at 14:07
  • 4
    Actually, I am learning from you
    – Santanu Boral
    Apr 27, 2017 at 14:08
  • 3
    Well sounds like SFSE is doing its job if we're all learning from each other. :)
    – Adrian Larson ♦
    Apr 27, 2017 at 14:38
5

Here's one brutally inefficient solution which ought to at least work:

IF(
    BEGINS(AccountNumber, '60000'),
    MID(AccountNumber, 6, LEN(AccountNumber)),
    IF(
        BEGINS(AccountNumber, '6000'),
        MID(AccountNumber, 5, LEN(AccountNumber)),
        IF(
            BEGINS(AccountNumber, '600'),
            MID(AccountNumber, 4, LEN(AccountNumber)),
            IF(
                BEGINS(AccountNumber, '60'),
                MID(AccountNumber, 3, LEN(AccountNumber)),
                IF(
                    BEGINS(AccountNumber, '6'),
                    MID(AccountNumber, 2, LEN(AccountNumber)),
                    AccountNumber
                )
            )
        )
    )
)

It'd probably be better to just make it a writeable field and use Apex or workflows. But if you want a formula, this one should do the trick. It should be obvious how to expand it to account for more leading zeroes if necessary.

Demonstration of the results:

Query Results

Improve this answer
0
1

Assuming that all of your accounts are 11 digits and all of them start with 6, the simplest solution is :

TEXT(VALUE(Customer_Account_No__c)-60000000000)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .