Removing last 11 characters from a field ?

[Zradene]

Hi guys, I'm sure I have done this before, but I am struggling to remember what the command is to do this.
I am importing an excel sales report from Ebay into my system.
The format is not ideal, but I can manipulate it using a macro within Excel so I can import into my system in the correct format, but one of the fields has 11 unwanted characters in an address field.
It seems that Ebay has just dumped some kind of reference data after the actual address in the same cell for no reason whatsoever. ie. Walton-on-the-Naze ebay376z5qa, so I need to remove these last 11 characters. This Ebay reference is always the last 11 characters in the cell.

Thanks

[armasoft]

try

left(fieldname, -11)

if you want to remove the trailing space as well either

left(fieldname, -12)

or

alltrim(left(fieldname, -11))

[Zradene]

Thank you so much. That works a treat.

[Zradene]

Hi again, I think I have jumped the gun. This is not working correctly. On my test record which had an address with 18 characters, all was perfect.
On a record with just 10 Characters in the address, there were still 9 of the unwanted characters showing. ?

[R Kenyon]

This will work

alltrim(left(fieldname,len(fieldname)-11))

[Zradene]

Hi, still not working.
Example
alltrim(left(Post_To_Address_2,len(Post_To_Address_2)-11))

This "Brake Road ebay1234565" ends up as This "Brake Road ebay12345"
This "Walton-on-the-Naze ebay1234565" ends up as This "Walton-on-the-Naze e"

[Ronald Anusiewicz]

From the interactive window

image.png

[madtowng]

My first attempt was wrong.
This presumes the string ebay is not included in the actual address.

post_to_address = "Brake Road ebay12345"
?stuff(post_to_address,at("ebay",post_to_address),11,chr(0))
= "Brake Road "
post_to_address = "Walton-on-the-Naze ebay1234565"
?stuff(post_to_address,at("ebay",post_to_address),11,chr(0))
= "Walton-on-the-Naze "

[Ronald Anusiewicz]

I just notice that you are returning the contents of a field. The field will return spaces. You need to trim these spaces before you can remove the last 11 characters.


image.png

[madtowng]

Based on Ron's observation, the following will work as well, eliminating everything after the string "ebay".

post_to_address = "Brake Road ebay12345 "
?stuff(post_to_address,at("EBAY",upper(post_to_address)),len(post_to_address),chr(0))
= "Walton-on-the-Naze "

post_to_address = "Walton-on-the-Naze ebay1234565+morerandomtext"

?stuff(post_to_address,at("EBAY",upper(post_to_address)),len(post_to_address),chr(0))
= "Walton-on-the-Naze "

[madtowng]

This option will remove the last "ebay" and any characters to the right of it.
It also allows for various capitalization.
If you want to eliminate the trailing space(s), wrap the code in alltrim().

post_to_address = "Brake Road ebay12345 "

?stuff(post_to_address,at("EBAY",upper(post_to_address),-1),len(post_to_address),chr(0))
= "Brake Road "

post_to_address = "Walton-on-the-Naze ebay1234565+morerandomtext"

?stuff(post_to_address,at("EBAY",upper(post_to_address),-1),len(post_to_address),chr(0))
= "Walton-on-the-Naze "

post_to_address = "123 Ebay Avenue ebay12345abcde"
?stuff(post_to_address,at("EBAY",upper(post_to_address),-1),len(post_to_address),chr(0))
= "123 Ebay Avenue "

[Zradene]

Hi my friend. Thank you so much for your kind help. I will check this out first thing in the morning. Best regards.

[Zradene]

Thank you so much, that works well. I am most grateful

[madtowng]

Glad to help where I can.
Alpha Anywhere is quite resilient and should be able to do the work without excel.
Reach out when you decide to try to automate the conversion process.

[Zradene]

Thank you. Just one last question, is it possible to convert that code to work in a Calc field by any chance ?