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])
Improve this question
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
    Commented Jun 10, 2019 at 12:02

1 Answer 1

Reset to default
3

Try using below formula, this works for me:

=IF(ISNUMBER(FIND(",",[TestField])),REPLACE([TestField],FIND(",",[TestField]),1,"_"),[TestField])
Improve this answer
16
  • Have you tries this? Try this and let me know if you face any issue.
    – Ganesh Sanap - MVP
    Commented Jun 10, 2019 at 12:40
  • Worked like a charm, thank you very much!
    – Benny Blanco
    Commented Jun 10, 2019 at 12:45
  • Welcome. Please upvote and accept the answer if it helped you.
    – Ganesh Sanap - MVP
    Commented Jun 10, 2019 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?
    – Benny Blanco
    Commented Jul 17, 2019 at 13:30
  • 1
    Thank you. Opened a new question for that if you want to have the answer there:
    – Benny Blanco
    Commented Jul 18, 2019 at 7:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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