# Extract last element in a string array **URL:** \ **Category:** Q&A\ **Tags:** calculations, Business-Intelligence-Engineer, quick-sight\ **Created:** [January 26, 2024, 4:10am UTC](https://community.amazonquick.com/t/extract-last-element-in-a-string-array/24259 "2024-01-26T04:10:02Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![JMthinkrf](https://avatars.discourse-cdn.com/v4/letter/j/c77e96/32.png) [@JMthinkrf](https://community.amazonquick.com/u/JMthinkrf)\ **Post date:** [January 26, 2024, 4:10am UTC](https://community.amazonquick.com/t/extract-last-element-in-a-string-array/24259/1 "2024-01-26T04:10:03Z")
I have an address field for which I am trying to extract the country during data preparation. For example: 123 West St, Washington, DC 20005, USA or 100 Main St STE 700, Office 754, Washington, DC 20004, USA Note that they don’t have the same number of elements in the address. I want to extract that last element in order to get the country. I have tried using: split( {address}, “,”, strlen({address})-strlen(replace({address}, “,” ,“” )) ) however, I get an error when trying to use the strlen/replace combination in the split function in data preparation. I have no control over the form of the address I receive, so have to deal with it as is. Any suggestions for how I extract that last element in a string? ---
**Author:** ![ErikG](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/erikg/32/14524_2.png) [@ErikG](https://community.amazonquick.com/u/ErikG)\ **Post date:** [January 26, 2024, 8:14am UTC](https://community.amazonquick.com/t/extract-last-element-in-a-string-array/24259/2 "2024-01-26T08:14:36Z")
Hi @JMthinkrf i tried you formula with one string you provided. ![grafik](https://d1wl4jav5dp96l.cloudfront.net/original/3X/7/b/7b11fdaa88a8d3b26ae2775074f7ac46d0b38cbe.png) BR ---
**Author:** ![robert.eibers](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/robert.eibers/32/17657_2.png) [@robert.eibers](https://community.amazonquick.com/u/robert.eibers)\ **Post date:** [January 26, 2024, 8:27am UTC](https://community.amazonquick.com/t/extract-last-element-in-a-string-array/24259/3 "2024-01-26T08:27:41Z")
It works for me too, you need to +1 at the end, then it should work: split( {1}, ‘,’, strlen({1})-strlen(replace({1}, ‘,’ ,‘’ ))+1 ) ---
**Author:** ![JMthinkrf](https://avatars.discourse-cdn.com/v4/letter/j/c77e96/32.png) [@JMthinkrf](https://community.amazonquick.com/u/JMthinkrf)\ **Post date:** [January 26, 2024, 1:29pm UTC](https://community.amazonquick.com/t/extract-last-element-in-a-string-array/24259/4 "2024-01-26T13:29:14Z")
Thank you both. There must be something odd with my dataset. When I try with a static string like in your code above, it works fine. But when I try it substitute with the actual field name {address}, and then try to publish it, I get ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/a/8/a8e13f8f23cb75f768b1f3fdf00416cb079540e0.png) {address} is type string. The formula works fine when I use: split( {address}, “,”, 4 ) I’ll keep trying stuff. Thanks again for the advice. ---
**Author:** ![ErikG](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/erikg/32/14524_2.png) [@ErikG](https://community.amazonquick.com/u/ErikG)\ **Post date:** [January 26, 2024, 1:37pm UTC](https://community.amazonquick.com/t/extract-last-element-in-a-string-array/24259/5 "2024-01-26T13:37:43Z")
Do you try on dataset or analysis level? could you test the other option? ---
**Author:** ![DeepakS](https://avatars.discourse-cdn.com/v4/letter/d/b2d939/32.png) [@DeepakS](https://community.amazonquick.com/u/DeepakS)\ **Post date:** [February 1, 2024, 2:07pm UTC](https://community.amazonquick.com/t/extract-last-element-in-a-string-array/24259/6 "2024-02-01T14:07:06Z")
Hi @JMthinkrf Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Sight Community Q&A Forum! ---
**Author:** ![JMthinkrf](https://avatars.discourse-cdn.com/v4/letter/j/c77e96/32.png) [@JMthinkrf](https://community.amazonquick.com/u/JMthinkrf)\ **Post date:** [February 5, 2024, 4:56pm UTC](https://community.amazonquick.com/t/extract-last-element-in-a-string-array/24259/7 "2024-02-05T16:56:55Z")
Unfortunately, no it did not work in a calculated field during data preparation. I didn’t get an error when I used a static string like in ErikG’s example, but when I substituted that static string with a string field, I got an error. I ended up using nested ifelse statements to extract the last field based upon the number of commas: > ifelse(isnull(split(address,“,”,7)), > ifelse(isnull(split(address,“,”,6)), > ifelse(isnull(split(address,“,”,5)), > ifelse(isnull(split(address,“,”,4)), > ifelse(isnull(split(address,“,”,3)), > trim(split(address,“,”,2)), > trim(split(address,“,”,3)) > ), > trim(split(address,“,”,4)) > ), > trim(split(address,“,”,5)) > ), > trim(split(address,“,”,6)) > ), > trim(split(address,“,”,7)) > ) Not terribly elegant, but it worked.