Regex Formula

[BRRLL99]

I have following data with trailing spaces, the spaces between each word is more than 2 spaces 

Field1
1000100034      APPLE/BUY      23456     001     800
1000100067      APPLE/BUY     23456     001     800
1000100099         VISA       *SAM      LI       23456     001     800
898998900     SAM 2     3242      900       700
1000100099      UNIQUE         *TEAM      UI      23456     001    800

Expected output : I would like considered if any string it considered as one set and delimiter |

1000100034|APPLE/BUY|23456|001|800
1000100067|APPLE/BUY|23456|001|800
1000100099|VISA*SAM LI|23456|001|800
898998900|SAM|23242|900|700
1000100099|UNIQUE*TEAM UI|23456 001 800

as shown above row : 3 and row: 5 are considered as one set.

[Warcry]

Does this work?

[BRRLL99]

I dont want to split the data

as shown in the expected out 

All strings should be considered as one set to add delimiter " | "

[Warcry]

fyi, your data is tab delimited \t

[flying008 · Accepted solution]

Hi, @BRRLL99 

Try this formula, if can help you get your want, please mark it as a solution and give a like for more share.

REGEX_Replace(REGEX_Replace(REGEX_Replace([Field1], '(?<=[A-Z]\s\d)\s+|\s+(?=\*)', ''), '(?<=[A-Z])\s+(?=[A-Z])', ' '), '\s{2,}|(?<=[A-Z])\s(?=\d)', '|')

Field1	Txt
1000100034      APPLE/BUY      23456     001     800	1000100034|APPLE/BUY|23456|001|800
1000100067      APPLE/BUY     23456     001     800	1000100067|APPLE/BUY|23456|001|800
1000100099         VISA       *SAM      LI       23456     001     800	1000100099|VISA*SAM LI|23456|001|800
898998900     SAM 2     3242      900       700	898998900|SAM|23242|900|700
1000100099      UNIQUE         *TEAM      UI      23456     001    800	1000100099|UNIQUE*TEAM UI|23456|001|800

[Qiu]

@BRRLL99 
We can use the Text to Column and Summarize tool as below.

[Warcry]

 

[Warcry]