Remove extra pipes from concatenated data

[haymarketpaul]

I have a number of concatenated pipe delimited job titles like the following

Job Title1	Job Title2	Job Title3	Job Title4	Job Title5
	Title2	Title3	Title4	Title5
Title1		Title3	Title4
	Title2			Title5

As you can see above some job titles are missing which means the data ends up like this with extra pipes in it...

|Title2|Title3|Title4|Title5

Title||Title3|Title4|

|Title2|||Title5

What i need to do is strip out the extra pipes so the data would end up looking like

Title2|Title3|Title4|Title5

Title1|Title3|Title4

Title2|Title5

Any ideas?

[antoniotiman]

Hi Paul,

try

=Replace(Replace('|Title2|Title3|Title4|Title5Title||Title3|Title4||Title2|||Title5','||','|'),'||','|')

Regards,

Antonio

[swuehl · Accepted solution]

LOAD

Concat(Pipe,'|') as Pipes

WHERE len(Pipe)

GROUP BY ID;

LOAD Subfield(Pipes,'|') as Pipe, recno() as ID  INLINE [

Pipes

|Title2|Title3|Title4|Title5

Title||Title3|Title4|

|Title2|||Title5

];

[sunny_talwar]

Very nice swuehl‌

[haymarketpaul]

Awesome - Thank you