Question Re: Remove last carriage return from address string in Technology Q&A

Remove last carriage return from address string

lsauser — Mon, 10 Aug 2020 04:13:06 GMT

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.

Re: Remove last carriage return from address string

DellSC — Mon, 10 Aug 2020 13:47:42 GMT

Try something like this:

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

-Dell

Re: Remove last carriage return from address string

lsauser — Mon, 10 Aug 2020 23:46:15 GMT

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 🙂 )

Re: Remove last carriage return from address string

msundararaja_perumal — Tue, 11 Aug 2020 02:13:15 GMT

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.

Re: Remove last carriage return from address string

DellSC — Tue, 11 Aug 2020 13:56:00 GMT

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}

Re: Remove last carriage return from address string

former_member308222 — Tue, 11 Aug 2020 14:00:25 GMT

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

Re: Remove last carriage return from address string

lsauser — Wed, 12 Aug 2020 05:18:37 GMT

Thanks for your input rajkumar.mane,

Wont this only work with that example address i provided?

Regards,

Nick

Re: Remove last carriage return from address string

lsauser — Wed, 12 Aug 2020 23:50:33 GMT

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

Re: Remove last carriage return from address string

DellSC — Thu, 13 Aug 2020 13:29:19 GMT

I missed a closing parenthesis:

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

Re: Remove last carriage return from address string

lsauser — Thu, 13 Aug 2020 23:22:55 GMT

Hey dell.stinnett-christy ,

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

Regards,

Nick

Re: Remove last carriage return from address string

Johan_Hakkesteegt — Fri, 14 Aug 2020 12:30:38 GMT

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.

Re: Remove last carriage return from address string

DellSC — Fri, 14 Aug 2020 13:25:28 GMT

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

Re: Remove last carriage return from address string

lsauser — Tue, 18 Aug 2020 02:25:26 GMT

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...

Re: Remove last carriage return from address string

lsauser — Tue, 18 Aug 2020 02:28:33 GMT

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

Re: Remove last carriage return from address string

lsauser — Tue, 18 Aug 2020 03:18:04 GMT

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

Re: Remove last carriage return from address string

DellSC — Tue, 18 Aug 2020 15:17:01 GMT

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

Re: Remove last carriage return from address string

lsauser — Tue, 18 Aug 2020 23:13:38 GMT

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