Deleting text from each end of a line of text

[mchadwick]

Hi, I have an address I would like to extract out of a field. This is what my data currently looks like:



City\Address\state\Country
Dallas\1234 Bob Street\TX\USA

I would like to end up with this:

Address
1234 Bob Street

I have used this; Left([Device],InStr([Device],".")-1)
to remove everything from a dot on to the end, but not sure how to do it in the middle. The field name is "[Address]

[InsuranceGuy]

Mid([address],InStr([ADDRESS],"\")+1,InStr(InStr([ADDRESS],"\")+1,[ADDRESS],"\")-(InStr([ADDRESS],"\")+1))

[pbaldy]

What dot?

Personally, if the format is consistent I'd probably create a public function that took the field plus a position as arguments. By position I mean the 1st, 2nd, 3rd or 4th piece of info. The function would use the Split() function to break the string down to the 4 parts. The position argument would let me pull any of the pieces. For example to get what you requested (the Split function returns a zero-based array, so the second bit is 1)

Whatever = MyFunction(Address, 1)

[mchadwick]

This string deletes all the data in the Address column
here is more example data:

Address
BOULDER/6300 DIAGONAL HIGHWAY/CO/USA
BOULDER/6300 DIAGONAL HIGHWAY/CO/USA
6TH OF OCTOBER CITY/RAYA DATA CENTER 26TH JULY ST. TOURISTIC ZONE//EGY
6TH OF OCTOBER CITY/RAYA DATA CENTER 26TH JULY ST. TOURISTIC ZONE//EGY
ABU DHABI/CORNER OF HAMDAN STREET & MUROOR STREET. MINISTRY OF ECONOMY BLDG. 1ST FLOOR - TOWER A//U
ABU DHABI/CORNER OF HAMDAN STREET & MUROOR STREET. MINISTRY OF ECONOMY BLDG. 1ST FLOOR - TOWER A//U
ADANA/RESAT BEY MH. ATATURK CD. NO:22 GEN ISM. NO: 22 K:6 D:18 SEYHAN//TUR
ADANA/RESAT BEY MH. ATATURK CD. NO:22 GEN ISM. NO: 22 K:6 D:18 SEYHAN//TUR
ALEXANDRIA/9 AL OMARAA TOWER BUILDING FIRST FLOOR ORABI SQUARE//EGY
ALEXANDRIA/9 AL OMARAA TOWER BUILDING FIRST FLOOR ORABI SQUARE//EGY

[mchadwick]

What about an expression that will go to the last "/" and delete it and everything after it and then run it again to delete the state and then run another query to delete everything before the first "/"?

[June7]

Examples in 1st post are different from 4th post. One has \ and other has /.

Change the \ to / in the suggested expression.

[mchadwick]

I should not have missed that. thank you very much it did the trick.