function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
cldavecldave 

Formula to Extract only the Numbers from a field

Hi,

I have a field which contains Social Security # and in the US it's formated this way: EX: 123-45-7890

Now i need to create a formula field to show only the numbers from that field without any other character like "-"

So result should be ;EX: 123457890

Can anyone pls help me with the syntax?

Thank you in advance
Best Answer chosen by cldave
Shashikant SharmaShashikant Sharma
You formula is :  
SUBSTITUTE(SSN__c, '-', '')
Thanks
Shashikant

All Answers

phiberoptikphiberoptik
If this is a scenario where the SSN is always in US format, then:
  
LEFT(Social_Security_Number__c, 3) & MID(Social_Security_Number__c,5,2) & RIGHT(Social_Security_Number__c, 4)

(remember to replace Social_Security_Number__c with the field name for your actual field.
Shashikant SharmaShashikant Sharma
You formula is :  
SUBSTITUTE(SSN__c, '-', '')
Thanks
Shashikant
This was selected as the best answer
Shyama B SShyama B S
You can use this formula: 
SUBSTITUTE(Social_Security_Number__c,'-','')

And works only if you give the return type as Text and not Number. 

Thanks,
Shyama
phiberoptikphiberoptik
Good call Shash... much more efficient..
cldavecldave
Thank you very much guys! all your answers were good, but the substitute is exactly what I was looking for, in case Data Entry was not done properly for SSN . Ex: 12-3456789
 
Shashikant SharmaShashikant Sharma
@phiberoptik - Thanks, recently used so could recall :).

@cldave - Happy that it helped you.

Thanks
Suraj Tripathi 47Suraj Tripathi 47

Hi cldave,

You can try this:

SUBSTITUTE(SSN__c, '-', '')

Please mark it as Best Answer if it helps!

Thanks