How to extract 3 last words from an excel cell using a predefined delimiter?

[vaalf]

Hello Everyone,

Does anyone knows a formula to extract last three words of my data as displayed below:


Tahvili Sq., 17th St., No. 403,, Shiraz, Fars, IR
No. 31, Jafar Gholi Alley,West Nostrat,Tohid Sq.,Tehran,Tehran, IR
22 No - 2 West St - Seol St tehran, IR1995934733, IR
Qazi Asadollah Sq., No. 6, 1st Floor,, Kashan, Isfahan, IR
No 63, Abdoos Alley, Vaziri St,Tehran,Tehran, IR
No. 48, Shahid Mohsen Kashfi St., Beginning Of Shahid Madani Ave., Resalat Sq.Tehran, IR1634957514, IR
Faramarz Abbasi Sq., Baharan St., 7th Fajr Alley, No. 139,, Mashhad, Khorasan Razavi, IR
Javanh Blindalley,The Fifth,St Shikh Safi, Esfahan, Esfahan, IR
No. 11, East 15, South Shaghayegh, Ferdows Blvd., Sadeghieh Sq.,Tehran, IR1484983664, IR
Arghavan St - No 4, Esfahan, Esfahan, IR
Unit 2 .No 58 .Milad Cr.Azadshahr, Mashhad, Khorasan Razavi, IR
Mehrshahr, Eram Blvd., Milade 4 St., No.58,, Karaj,Tehran, IR
#48,2nd Fl.,Masoud Bazar,Zand Blvd., Shiraz, Fars, IR
Molla Sadra Ave. At Sheikh Bahai, Setade Farmandehi Sazemane Atashneshani Tehran, IR14358-63151, IR
Nabshe Meidane Shahid Mosavi Ghochani, Mashhad, Khorasan Razavai, IR
4th Floor, Khayyam Building, Moallem St.,, Rasht, Guilan, IR
No. 20, Sadoughi Blvd..Yazd, IR89197, IR
Khiaban Talaghani , Nabshe Khiaban Sepahbod Gharani Tehran, IR1581795411, IR
No. 51, West 188 St.,Tehranpars,,Tehran,Tehran, IR
No.7, Mirzaee Alley, Mojahedin St.,Tehran, IR11577, IR
Tehranmunicipality(1888) No.59 , Ansari Alley , Behesht St , Parkeshahr ,,Tehran,Tehran, IR
No. 27, 32th Gharani, Gharani Blvd.,, Mashhad, Khorasan Razavi, IR
No 25, Secend Laleh Alley, Mojahed Kabir Street, Laleh Sharghi Street, Jannat Abad,Tehran,Tehran, IR


I want them to be extracted like this:

Shiraz, Fars, IR
Tehran,Tehran, IR
tehran, IR1995934733, IR
Kashan, Isfahan, IR
Tehran,Tehran, IR
Tehran, IR1634957514, IR
Mashhad, Khorasan Razavi, IR
Esfahan, Esfahan, IR
Tehran, IR1484983664, IR
Esfahan, Esfahan, IR
Mashhad, Khorasan Razavi, IR
Karaj,Tehran, IR
Shiraz, Fars, IR
Tehran, IR14358-63151, IR
Mashhad, Khorasan Razavai, IR
Rasht, Guilan, IR
Yazd, IR89197, IR
Tehran, IR1581795411, IR
Tehran,Tehran, IR
Tehran, IR11577, IR
Tehran,Tehran, IR
Mashhad, Khorasan Razavi, IR
Tehran,Tehran, IR


As you see the delimiter is not always the same prior to third word to the last; it might be sometimes a comma, dot or even a space.

And sometimes the second word itself consist of two or more words with spaces in between them. The red color represent those cells.

And sometimes the second word itself consist of alphanumeric characters with a hyphen between them. The blue color represent those cells.

Your help is highly appreciated, guys.


Thanks,
Vaalf

[canapone]

Hi

this formula has a couple (maybe more) of limits: it cannot split correctly the string if comma is not present as delimiter (third string in your example) and often it adds too many "," to the output because it substiutes blank spaces into commas (again as in the third address in your example.

Please Login or Register  to view this content.

Anyway I hope it's a little help

Regards

[martindwilson]

i can nearly do it but those KhorasanRazavai mess thinks up
however i split it with text to columns delimited with comma ,space, and full stop(period) in the other box
then use formulas to pull the last 3 cells

[Fotis1991]

Hi

With your data in A1, give a try to this.

=TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";60));180))

[vaalf]

Hi

With your data in A1, give a try to this.

=TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";60));180))

Hi,

Thanks for posting a formula but it contains errors! could you check it out please?

Thanks,
vaalf

[martindwilson]

=trim(right(substitute(a1," ",rept(" ",60)),180))

[vaalf]

=trim(right(substitute(a1," ",rept(" ",60)),180))

Martin,

Thanks for your help. Your formula was the solution. My great thanks!

Thanks,
Vaalf