Formulae: Replacing substring in a text field

Phillipa · November 3, 2015 11:42AM

Hello,

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?

Thanks
Phillipa

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:
http://forums.visokio.com/discussion/2365

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.

Phillipa

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.

Welcome!

Categories

Ideas Parade

Tagged