Aug 31 2022 04:39 AM
So I am trying to extract the cities from the string, though I have resolved the issue, with an alternative formula, but my reason to ask this query, is that, I was earlier trying to use the REDUCE() Function to substitute multiple characters all at once, but it always gave me #VALUE! Error.
I am not able to understand or figure out, why I am getting this error, can anyone please let me know. Kindly refer the screenshot and markdown data as well, https://1drv.ms/x/s!AlEkksoPTc2chiMZHvUKkgK2gjCv?e=WIAWDF
| 2000 Orange Ave, Ste 650 Winter Park, FL 56789 | | 5665 S Broadway St Pittsburg, KS 65662 | | 1 DTC Parkway, Ste 700 Greenwood Village, CO 45111 | | 4545 Hyde Park Blvd., Houston, TX 74506 | | 5668 Lake Center Plaza #206 Potomac Falls, VA 41516 | | 898989 Worthington Blvd Frederick, MD 21564 | | D-46548 Wolfe St, 3rd, Floor. Fredericksburg, VA 24501 | | 100-D Foam St Ste B2, Monterey, CA 93954 | | 300 Cushman St. Suite 501, Fairbanks, AK 99451 | | 450 E Hopkins Ave, Ste 302 Aspen, CO 84511 | |
• Formula used in cell B1
=REDUCE(A1,{",","-","."},LAMBDA(x,y,SUBSTITUTE(x,y,"")))
• While I used to extract the cities, in cell C1
=LET(x,IFERROR(TAKE(TEXTSPLIT(TRIM(SUBSTITUTE(A1,RIGHT(A1,10),"")),{" ",",","-",".","Ave","Ste","St","Drive","Floor","Blvd"}),,-2),""), TEXTJOIN(" ",1,FILTER(x,NOT(ISNUMBER(--x)),"")))
Thank you very much for guidance !
Please see .gif
Aug 31 2022 04:57 AM - edited Aug 31 2022 04:58 AM
I have no errors if open the file in my locale
Perhaps you have another list separator, e.g. semicolon instead of comma
Aug 31 2022 04:59 AM
Aug 31 2022 05:53 AM
Are you on Excel Desktop or Excel for Web? If the latest, which regional settings do you use?
If you open the file attached to the previous post do you have errors? I have no ones in both sheets.
Aug 31 2022 05:59 AM - edited Aug 31 2022 06:01 AM
Tested here:
Excel web - OK
Excel desktop - OK
No #VALUE! errors. Using comma as list separator.
I do recall something vaguely similar with a simple MAP formula that was producing a #VALUE! error. I thought maybe I was spending too much time looking at the screen because the formula had been working then broke on re-calc. Haven't seen that one come back.
Aug 31 2022 05:59 AM
Aug 31 2022 06:02 AM
Did you open file attached to the post or your own file?
Aug 31 2022 06:03 AM
If your own file, try to re-enter formula (F2->Enter)
Aug 31 2022 06:04 AM
Aug 31 2022 06:07 AM
Aug 31 2022 06:12 AM
If #NAME? error most probably you have no such function in your version of Excel.
Do I understand correctly you DO NOT use Excel for web and work in Excel Desktop? If so, which version / channel?
Aug 31 2022 06:17 AM - edited Aug 31 2022 06:19 AM
Sir I'm using MS 365 latest version with Beta channel enabled. Version 2209 Build 15619.20000
Aug 31 2022 06:26 AM
@Mayukh_Bhattacharya
Sorry, I have no idea, not able to reproduce.
I'm on
Regional settings
Open your file, recalculate, it shows
If you try not with comma and semicolon, but substitute something like {"a", "b"}, you also have an error?
Aug 31 2022 06:29 AM
Aug 31 2022 06:35 AM
It may seem arbitrary but what does this formula return for you when placed in B1?
=REDUCE(A1,TEXT({",",".","-"},";;"),LAMBDA(x,y,SUBSTITUTE(x,y,"")))
Aug 31 2022 06:36 AM
Okay, but you said an error is on Excel Desktop and you don't use Excel for web
Aug 31 2022 07:55 AM
Aug 31 2022 08:09 AM
Aug 31 2022 08:11 AM
@Sergei Baklan Sir see this image its working but not working for those strings which I posted earlier, also MS Support guy was not able to help.
Aug 31 2022 08:30 AM
@Sergei Baklan Sir, strange same formula is working now, but not able to understand why it was not working then