RegEx remove special characters

[Newbee3]

Hey guys

This is my first post - I hope I am putting this in the right section. So here is my problem, on which I would very much appreciate your help:

I would like to remove a portion in the text depending on the value displayed:

Line item	Value
Net income/(loss)	300
Earnings/(loss) per share	-200

So ultimately I would need to say the text the following, after having worked on it:

Line item	Value
Net income	300
Loss per share	-200

The text always says either "net income/(loss) xxx xxx", "Earnings/(loss) xxxx xxx" or "diluted earnings/(loss) xxxx xxx" (x -> any kind of character). The only consistency is /(loss). I am not worried about the conditional part to it (IF c THEN d ELSE....) but more with the RegEX portion to correctly identify "/(loss)" and remove it OR - in case in case the number is negative - to remove the anything before "loss" and to stripp way the slash and the brackets.

[DavidP]

Edited: This can be done with regex like below. You can also do or with normal text functions like left() and findstring()

[DavidP]

I meant replace function, like this

[Newbee3]

Thank you very much @DavidP for your help!

With your solution I am now able to deal with the positive line items. But I seem to not able to understand how to solve the issue on the negative values. Please let me share with you the more detailed picture (all public available data - so no breach there). The texts would need to read:

a) Loss

b) Loss attributable to.....

c) Loss attributable to...

d) Basic loss per share

e) Diluted loss per share

Thank you in advance.

[OllieClarke · Accepted solution]

Hi @Newbee3 

In a formula tool you could write the following formula

IF [Value]>=0 THEN 

REPLACE([Line Item],'/(loss)','')

ELSE

REGEX_REPLACE([Line Item],'(?:Net )?\S+\/\(loss\)','Loss')

ENDIF

(instead of the filter/regex/union combo)