In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Regex Formula

BRRLL99
11 - Bolide

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.

7 REPLIES 7
Warcry
9 - Comet

Does this work?

BRRLL99
11 - Bolide

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
9 - Comet

fyi, your data is tab delimited \t

flying008
15 - Aurora

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)', '|')

 

 

 

 

 

Field1Txt
1000100034      APPLE/BUY      23456     001     8001000100034|APPLE/BUY|23456|001|800
1000100067      APPLE/BUY     23456     001     8001000100067|APPLE/BUY|23456|001|800
1000100099         VISA       *SAM      LI       23456     001     8001000100099|VISA*SAM LI|23456|001|800
898998900     SAM 2     3242      900       700898998900|SAM|23242|900|700
1000100099      UNIQUE         *TEAM      UI      23456     001    8001000100099|UNIQUE*TEAM UI|23456|001|800

 

Qiu
21 - Polaris
21 - Polaris

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

0306-BRRLL99.png

 

Warcry
9 - Comet

 

 

Warcry
9 - Comet

Screenshot 2025-03-06 002949.png

 

Labels
Top Solution Authors