REGEX

[BRRLL99]

INPUT :

field_1

Clearing Currency

Rate      Count      Table     Amount     Value    Value

ID      Credits                   Debits

Net       Merchandise Credit     35,0000

Net    Issuer Interchange   23,000DB

Net      Purchase Amount 10,000 GBP

Expected Output : it should add | whenever there is 2 spaces but only if contains Net | should be added before amount

i have tried this formula :

IF REGEX_Match([field_1], "^Net") THEN
REGEX_Replace(Trim([field_1]), "(\s{2,}|\t)+", "|") + "|"
ELSE
REGEX_Replace(Trim([field_1]), "(\s{2,}|\t)+", "|")
ENDIF

field_1

Clearing Currency|

Rate|Count|Table|Amount|Value|Value

ID|Credits|Debits

Net Merchandise Credit|35,000

Net Issuer Interchange|23,000DB

Net Purchase Amount|10,000 GBP

[ChrisTX]

This part is not easy to understand.  Can you re-word this:

Expected Output : it should add | whenever there is 2 spaces but only if contains Net | should be added before amount

Does the second table represent your expected output?

[BRRLL99]

Like you said

Yes 2nd table is my expected output

[SPetrie]

Would this work?

if Contains([field_1],"Net") then
REGEX_Replace(REGEX_Replace([field_1],'\s\s+'," "),'(.+)(\s)(\d.+)',"$1|$3")
else
REGEX_Replace([field_1],'\s\s+',"|")
endif

[LindonB]

How about this....

IF

    ISNULL([field_1]) THEN NULL()
ELSE
    TRIM(
        IF CONTAINS([field_1], "Net") THEN
            REPLACE(
                REGEX_Replace(
                    REGEX_Replace([field_1], "(\s+\d)", "|$1"),
                "\s+",
                " "),
            "| ","|")
        ELSE
            REGEX_Replace([field_1], "\s{2,}", "|")
        ENDIF
    )
ENDIF

A bit more complicated, but it....

1. When Null then keep Null.
2. When "Net" is not present, replaces multiple spaces with "|".
3. When "Net" is present...
   • Replaces multiple spaces with " ".
   • Adds "|" before the first number.
   • Removes the extra space that will be present before the "|"
4. Then trims all remaining white space from the ends.
   
   
   

[binuacs · Accepted solution]

@BRRLL99 another method

[flying008 · Accepted solution]

Hi, @BRRLL99 

FYI.

Replace(REGEX_Replace(REGEX_Replace(Trim([field_1]), '\s(?=\d)', '  '), '\s{2,}', '|'), 'Net|', 'Net ')

field_1	Get
Clearing Currency	Clearing Currency
Rate      Count      Table     Amount     Value    Value	Rate|Count|Table|Amount|Value|Value
ID      Credits                   Debits	ID|Credits|Debits
Net       Merchandise Credit     35,0000	Net Merchandise Credit|35,0000
Net    Issuer Interchange   23,000DB	Net Issuer Interchange|23,000DB
Net      Purchase Amount 10,000 GBP	Net Purchase Amount|10,000 GBP