Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: Replacing substring in a text field - Visokio Forums
Formulae: Replacing substring in a text field
  •     Phillipa November 3, 2015 11:42AM

    I have a text field with different strings.
    I am trying to replace the first 2 characters of each string.

    Specifically I want to replace "PT" with "m1"; "m1" with "m2"; "m2" with "m3" etc.

    I have seen that there is a REPLACE and REPLACEREGEX function, which I would be interested in using,
    but can't seem to get either of them to work correctly. Could someone help?

  •     paola November 3, 2015 1:35PM
    Depending on how many variations there are, you might be better off creating a data mapping spreadsheet, that will list all variations of the original text against the 'desired value', then merge it with your dataset.
    Demo and explanation here:

    Omniscope has function
    LEFT(text, num_chars)
    that might be helpful to create a formula, but search/replace later might be inefficient, in case "PT" appears after the first 2 characters.

  •     Phillipa November 4, 2015 4:02AM
    Hi Paola,

    Thanks for the comments.

    I used the following formula assuming PTetc doesn't come anywhere else in the text:

    IF(CONTAINS([Pivot series],"PT"),REPLACE([Pivot series],0, 2, "m1"),
    IF(CONTAINS([Pivot series],"m1"),REPLACE([Pivot series],0, 2, "m2"),
    IF(CONTAINS([Pivot series],"m2"),REPLACE([Pivot series],0, 2, "m3"),
    IF(CONTAINS([Pivot series],"m3"),REPLACE([Pivot series],0, 2, "m4"),
    IF(CONTAINS([Pivot series],"m4"),REPLACE([Pivot series],0, 2, "m5"),
    IF(CONTAINS([Pivot series],"m5"),REPLACE([Pivot series],0, 2, "m6"),
    IF(CONTAINS([Pivot series],"m6"),REPLACE([Pivot series],0, 2, "m7"),"")))))))

    However, I thought there might be a more elegant solution.

  •        daniel November 4, 2015 4:46PM
    I would suggest using startswith() instead of contains.
  •     Phillipa November 5, 2015 8:40AM
    Thanks Daniel. I wasn't aware of the startswith function.


It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership