How to remove trailing spaces only

[JM39]

Hi Everyone

I have an issue I'm struggling with to remove only trailing spaces from two strings. I have two columns which may or may not contain data. Where there is data in either column, it may or may not have trailing spaces, leading spaces or both. What I want to do is to remove only the trailing spaces (keeping the leading spaces) and concatenate the two columns. So for example, my source data might look like this:

COLUMN1 COLUMN2

'First Bank ' ' Ltd '

'Second Bank '

'Third Bank' 'Ltd '

So I just need to remove the trailing spaces. If I remove them all, the result of the concatenation on the first row would be

'First BankLtd'

Whereas it should be

'First Bank Ltd' (keeping the leading space in column2)

I thought about removing all leading and trailing, then adding a space back in between the concatenated values, but if there's no value in column2 that essentially leaves me with a trailing space on column1!

Is it possible to do? Thank you!

[JM39]

So I thought I'd come up with a solution with this:

row2.Column2==null ?

row2.Column1 :

row2.Column1+" "+row2.Column2 

So I think I'm saying that if column2 is null then use the value in column1, else concatenate column1 & column2, separated by a space.

In the advanced settings in the source file, I also checked column1 & column2 to trim.

But it seems to be ignoring the first condition row2.Column2==null ?

So where I've got null values in column2 it's using the value from column1, but I've ended up with a trailing space as if it's concatenated column1 & 2 with a space in between.

I'm wondering if it's not actually null values in column2, but when I look at the delimited source file, there's definitely no spaces or anything else in that column.

Any help appreciated!