How to strip out a semi-random text pattern? (1 Viewer)

[peskywinnets]

For some bizarre reason Ebay has just started randomly adding to the the following text to StreetAddress2 field

(the following are just 3 random examples)..

Ebayj4jvksc

Ebayv4jmfz6

Ebayp4jjqv3

...it's always Ebay...followed by 7 random characters (therefore 11 characters in total)...when they are present, they are always at the end of the StreetAddress2 field (but sometimes they aren't there). So for example, for StreetAddress2 I get....

11 Station Road Ebayp4jjqv3

....I need just the 11 Station Road bit

So I'm looking for an elegant way to strip the 11 rogue characters out (when they appear) via a query ...top tips warmly received!

here's my starter...

iif(left(Right([StreetAddress2],11),4)="Ebay", do something here, [StreetAddress2])

 

[theDBguy]

Hi. If you don't ever expect "ebay" to show up in anyone's address2, then you can use the InStr() function to look for it and take it out.

 

[Isaac]

Replace(InputStringVariable,mid(InputStringVariable,instr(1,InputStringVariable,"Ebay")),"")

 

[Isaac]

dbGuy makes a good point, so I should add that my solution, as well, requires you to stop and consider that if someone's address contains text like "Pointebay", you might do some damage. You could probably eliminate 90% of this risk by adding a space to the beginning of my "Ebay" in the above function, and adjust accordingly. That would only leave you with the risk that someone's address contains the text " Ebay*", including the space, which hopefully would be a one in a million thing.

 

[peskywinnets]

Thanks guys, this may not be that elegant, but you can perhaps see my direction of travel, a query to isolate chunks of ShippingAddress2...

so using the above query, I just need to test for the existence of "Ebay", so something like this...

MyShippingAddress2: IIf([Left4]="Ebay",Replace([ShippingAddress2],[Right11],""))

...which seems to work, but I'm sure there's a more elegant way?

 

[Gasman]

Look for the position of the last space and delete from there?

 

[Isaac]

Thanks guys, this may not be that elegant, but you can perhaps see my direction of travel, a query to isolate chunks of ShippingAddress2...

View attachment 84979

so using the above query, I just need to test for the existence of "Ebay", so something like this...

MyShippingAddress2: IIf([Left4]="Ebay",Replace([ShippingAddress2],[Right11],""))

...which seems to work, but I'm sure there's a more elegant way?

As to anyone's opinion on "elegance", I have no idea, but I did post a solution that works....

 

[Gasman]

As to anyone's opinion on "elegance", I have no idea, but I did post a solution that works....

Perhaps even look for " Ebay" ?

 

[peskywinnets]

As to anyone's opinion on "elegance", I have no idea, but I did post a solution that works....

Indeed you did, it works (thanks!)...

CorrectedShippingAddress2: Replace([ShippingAddress2],Mid([ShippingAddress2],InStr(1,[ShippingAddress2],"Ebay")),"")

...but if I understood correct, wouldn't that cause problems if the word Ebay appeared anywhere in StreetAddress2 legitmately?

Would I not need to test that "Ebay" is only present starting at exactly 11 characters from the end of the StreetAddress2 field?

 

[Isaac]

...but if I understood correct, wouldn't that cause problems if the word Ebay appeared anythere in StreetAddress2 legitmately?

Yes. You could reduce that small risk much further if you tested for [space]Ebay, though.

I probably need to test that "Ebay" is only present starting at 11 characters from the end of the StreetAddress2 field?

If the "problem" text is always the last 11 characters, then definitely just choose the left(Input,len(Input)-11) of the Input....but only if the right 11 char's are bad in the first place.
Perhaps: iif(instr(1,Right(Input,11),"Ebay")>0,left(Input,len(Input)-11),Input)

You could get more and more elaborate to try to get to a place where there is zero chance that all of this is part of someone's address, but from what you've described, the way I understand it, that approach would probably get you to "less chance than winning the lottery" area, or thereabouts.

 

[peskywinnets]

Yes. You could reduce that small risk much further if you tested for [space]Ebay, though.

If the "problem" text is always the last 11 characters, then definitely just choose the left(Input,len(Input)-11) of the Input....but only if the right 11 char's are bad in the first place.
Perhaps: iif(instr(1,Right(Input,11),"Ebay")>0,left(Input,len(Input)-11),Input)

You could get more and more elaborate to try to get to a place where there is zero chance that all of this is part of someone's address, but from what you've described, the way I understand it, that approach would probably get you to "less chance than winning the lottery" area, or thereabouts.

Many thanks...you've been of great help

 

[Isaac]

 

[jdraw]

A little late, but a test routine that may be of some use.

Sub peskySep20()
          Dim a(4) As String
          Dim i As Integer
          Dim StreetAddress2 As String

10        a(0) = "213 PointeBay Road Ebayp4jjqv3"
20        a(1) = "2 Pointe Bay Rd. Ebayj4ndqv3"
30        a(2) = "13 Pineapple Way Ebay9eedkrr"
40        a(3) = "21 Sussex Drive West Ebay3vqquyn"
50        a(4) = "13 Orange St. "

60        For i = LBound(a) To UBound(a)
70            StreetAddress2 = a(i)
80            Debug.Print IIf(StreetAddress2 Like "* Ebay*", Mid(StreetAddress2, 1, InStr(StreetAddress2, " Ebay")), StreetAddress2)
90        Next i
End Sub

Good to see you back Pesky.

 

[peskywinnets]

Good to see you back Pesky.

Thanks for the input (& the welcome back)

It transpires that (since Saturday) Ebay are now adding this unique code to shipping address labels (to help assist with Item Not Received claims...how thgis helps I have no idea!)...I've no issue with this unique reference going on the generated shipping label, but I still need to strip it out foir the invoice.

Many thanks for all your input....I'm all sorted now