2

I am trying to replace a comma from a single line of text field with an underscore(this I managed to do), but I want to replace it if a certain criteria is met - if the value in the field contains a comma to replace it with an underscore and if it doesn't, to leave the field with the value it already has. Here's the formula I managed to put together and only the part where it replaces the comma with an underscore works. If the field doesn't contain any commas though it just puts #VALUE! inside of the calculated field.

=IF((FIND(",",[TestField])),REPLACE([TestField],FIND(",",[TestField]),1,"_"),[TestField])
1
  • you could try using a SP2013 workflow to do this kind of action if that is an option. There is a command to find a character in a substring.
    – Tally
    Jun 10 '19 at 12:02
3

Try using below formula, this works for me:

=IF(ISNUMBER(FIND(",",[TestField])),REPLACE([TestField],FIND(",",[TestField]),1,"_"),[TestField])
16
  • Have you tries this? Try this and let me know if you face any issue. Jun 10 '19 at 12:40
  • Worked like a charm, thank you very much! Jun 10 '19 at 12:45
  • Welcome. Please upvote and accept the answer if it helped you. Jun 10 '19 at 12:45
  • Hi Ganesh, want to ask you how to edit the formula you gave me to work for more than 1 comma, let's say we have 1,023,012 ? For the moment it replaces the first comma, but doesn't replace the second. How can we have it replace all commas, no matter of the number? Jul 17 '19 at 13:30
  • 1
    Thank you. Opened a new question for that if you want to have the answer there: Jul 18 '19 at 7:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.