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.

[Johan_Hakkesteegt]

Hi Nick,

Perhaps you can make the STRING_SPLIT function work for you:

SELECT value 
FROM STRING_SPLIT('123 Fake St
Suburb, VIC
4512', (CHRW(13)+CHRW(10)));

Regards,

Johan

P.S. If this is for use with SAP Business One, you should look into using the RDR12, DLN12, INV12 tables. These tables hold the separated address components.

[lsauser]

Hey Johan,

Thanks for the input on this one. Unfortunately i would prefer if this could be done with a crystal formula. I didnt write this particular report myself and the way they have done the commands and table joins is a bit beyond me.

A crystal formula will be the most efficient way for this one.

Regards,

Nick

[former_member308222]

Hi,

Please try with below code but condition is you know the last some characters from data.

With using below coding I differentiate on two parts, part 1 is for left side all data and part 2 is for replace chr(13) to ' ').

Part 1

left ("123 Fake St" & chr(13) & "Suburb, VIC" & chr(13) & "4512",len("123 Fake St" & chr(13) & "Suburb, VIC" & chr(13) & "4512")-5 )

Part 2

Replace ("123 Fake St" & chr(13) & "Suburb, VIC" & chr(13) & "4512",chr(13), ' ', len("123 Fake St" & chr(13) & "Suburb, VIC" & chr(13) & "4512")-4)

Whole query

left ("123 Fake St" & chr(13) & "Suburb, VIC" & chr(13) & "4512",len("123 Fake St" & chr(13) & "Suburb, VIC" & chr(13) & "4512")-5 ) & Replace ("123 Fake St" & chr(13) & "Suburb, VIC" & chr(13) & "4512",chr(13), ' ', len("123 Fake St" & chr(13) & "Suburb, VIC" & chr(13) & "4512")-4)

If any further query please inform.

- Rajkumar Mane

[lsauser]

Thanks for your input rajkumar.mane,

Wont this only work with that example address i provided?

Regards,

Nick

[msundararaja_perumal]

Hello,

I believe you are using the crystal report for SAP Business One, I suggest you directly pick the address from the BP Master address lines in order to avoid this issue.

Thanks.

[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