Remove last carriage return from address string

[lsauser]

Hi all,

I have an address block like such;

123 Fake St
Suburb, VIC
4512

I want to only remove the last carriage return so it looks like;

123 Fake St
Suburb VIC 4512

I know i can use the following to get rid of all the carriage returns which does put it all on one line and i have played around with this with no luck getting it right. I have a feeling i need to use a combination of instr, strreverse and right but havent been able to get the logic to work.

replace({Header.Address},chrw(13),'')

As always, any help is greatly appreciated.

[DellSC]

Try something like this:

If Right({Header.Address}, 1) = chrw(13) then
  left({Header.Address}, len({Header.Address}) - 1
else
  {Header.Address}

-Dell

[lsauser]

Hey dell.stinnett-christyy,

No luck with that, it didnt change it at all, still the full address block with all carriage returns.

(also you missed a closing bracket in the code i had to add in 🙂 )

[DellSC]

Actually, if you're dealing with Windows text, the end of line is two characters - CR/LF (carriage return, line feed) In that case, you can change the formula to this:

If Right({Header.Address}, 2) = (chrw(13) + chrw(10)) then
  left({Header.Address}, len({Header.Address})) - 2
else
  {Header.Address}

[lsauser]

Error: A number, currency, amount, date, time or date-time is required here.

[DellSC]

I missed a closing parenthesis:

IfRight({Header.Address},1)= chrw(13)then
  left({Header.Address},len({Header.Address})-1)
else
  {Header.Address}

[lsauser]

Hey dell.stinnett-christy ,

This didnt change it at all, it still looks like the original

Regards,

Nick

[DellSC]

Sorry... I meant to do this:

If Right({Header.Address},2)=(chrw(13)+ chrw(10)) then
  left({Header.Address},len({Header.Address})-2)
else
  {Header.Address}

The second close parenthesis in the second line needed to be at the end of the line.

-Dell

[lsauser]

Hey dell.stinnett-christy ,

Still no luck with this code, i modified it to check the condition and to give me a "Yes" or "No" answer whether a chrw(13) or (10) was picked up but it only ever returned "No" regardless of which number i used for the character.

This is weird considering if i use the replace function in my original post, it does actually replace all of them, including the one between state and postcode i am trying to target...

[lsauser]

I even made a formula to give me right({Header.Address},6) which returned a blank character, i then made another formula to give me the ASCII code for the returned character and it was 13..

Now when i went to check IF right({Header.Address},6) was = chr(13) it came back "No"

I dont know why this is proving so difficult.

Regards,

Nick

[DellSC]

Let's try something different...

StrReverse(Replace(Replace(StrReverse({Header.Address}), chr(10), "", 1, 1), chr(13), "", 1, 1))

This will reverse the string so that the last characters are now the first. Then it will replace the first occurrence of chr(10) and chr(13) with blanks, then it will reverse the string back to its original order.

-Dell

[lsauser]

That worked! Thanks so much Dell, ive been scratching my head over this for a while now. I tried applying this logic but couldnt get the code right myself.

Regards,

Nick