Separating character cells with a specific rule

[mukul62116]

Hello All,

I have a large set of Addresses in a similar format where it has Country name in the last. I want to just write a formula in order to just separate the Country name from the whole address.
Please look at a sample below of what I am looking for.

ADDRESSES
North Front Street, New Ulm, Brown County, Minnesota, 56073, United States of America
Janice McTighe Centre, 2050, 21 Street NE, Vista Heights, Calgary, Census Divsion No. 6, Alberta, T2E 6S5, Canada
West Little York Road, Satsuma, Harris County, Texas, 77040, United States of America
Walker Drive, Grahamsville, Woodhill, Brampton, Peel Region, Ontario, L6T 5M3, Canada
Walker Drive, Grahamsville, Woodhill, Brampton, Peel Region, Ontario, L6T 5M3, Canada
Walker Drive, Grahamsville, Woodhill, Brampton, Peel Region, Ontario, L6T 5M3, Canada
Sterling Drive South, Sterling Heights, Macomb County, Michigan, 48312, United States of America
Conestoga Boulevard, City of Cambridge, Regional Municipality of Waterloo, Ontario, N1R 6J8, Canada


So, I am looking to extract All the countries in the next column. All the countries are after the last 'Comma' in the cells.

Thank you in advance.

best regards

[dosydos]

assuming your data starts in A1 and goes down, put this formula in B1 and drag down.

=TRIM(RIGHT(A1,LEN(A1)-FIND("+",SUBSTITUTE(A1,",","+",LEN(A1)-LEN(SUBSTITUTE(A1,",",))))))

[dosydos]

Glad it worked, thanks for the Rep