Why does REDUCE Function gives VALUE Error

Iron Contributor

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 

 

FcJaL.png

 

| 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

 

xiQgo.gif

 

27 Replies

@Mayukh_Bhattacharya 

I have no errors if open the file in my locale

image.png

Perhaps you have another list separator, e.g. semicolon instead of comma

No Sir, I use comma only. You can see the .gif it is not working, if you open the excel workbook then on sheet 2 also i created some random text where it is not working, very strange and weird

@Mayukh_Bhattacharya 

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.

 

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.

Sir its not working both places Excel Desktop as well as in Web. Sir workbook is editable mode

@Mayukh_Bhattacharya 

Did you open file attached to the post or your own file?

@Mayukh_Bhattacharya 

If your own file, try to re-enter formula (F2->Enter)

Yes sir the one which you posted it shows me Name error
Sir i did that sir. As you can see newer functions like take and textsplit are working but this one suddenly not working. I have raised the issue with ms support they are also confused not able to find a solution

@Mayukh_Bhattacharya 

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?

Sir I'm using MS 365 latest version with Beta channel enabled. Version 2209 Build 15619.20000

@Mayukh_Bhattacharya 
Sorry, I have no idea, not able to reproduce.

I'm on

image.png

Regional settings

image.png

Open your file, recalculate, it shows

image.png

If you try not with comma and semicolon, but substitute something like {"a", "b"}, you also have an error?

Sir let me repair excel online. Because I found its working online now and i will get back to you.

@Mayukh_Bhattacharya 

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,"")))

@Mayukh_Bhattacharya 

Okay, but you said an error is on Excel Desktop and you don't use Excel for web

Sir, it removes all those characters from a string
Ok Sir, I found out, that I can use REDUCE() Function anywhere, i.e. I have used that specific formula in other workbooks and as well as in new workbook as well, but it is not working for this specific texts strings. For those US Addresses

@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.

 

Mayukh_Bhattacharya_0-1661958656087.png

 

@Sergei Baklan Sir, strange same formula is working now, but not able to understand why it was not working then

 

Mayukh_Bhattacharya_0-1661959772303.png