#NAME error (was "Split_Into_Characters it looks like vba or macro but missing")

[babysas]

Hi, i've downloaded a spreadsheet and have #NAME error everywhere it looks like Split_Into_Characters function doesn't work from Prompt_to_Tokens sheet is the script or macro missing?

[ianand]

Are you using it with localization or locale set to something other than English?

https://github.com/ianand/spreadsheets-are-all-you-need/issues/6

https://trulyoffice.com/blogs/truly-top-picks/how-to-fix-name-error-excel#:~:text=Localization%20and%20Language%20Settings:%20Excel%20may%20use,the%20%23NAME%20error%2C%20other%20underlying%20reasons%20exist.

[babysas]

I've found the cause Named formulas in name manager has strange functions googling shows it works only with excel 365 i have 2021..

Thanks.

[ianand]

Ah. As you've identified the issue there is that Excel 2016 doesn't have the lambda function which is only in Excel 365 per the documentation. A lot of the named functions make use of lambda as you can see.

:(

BTW the text of the function Split_Into_Characters is:

=LAMBDA(word, MID(word,SEQUENCE(1,LEN(word)),1))

Hmm.. Have you tried asking ChatGPT to convert the formulas to ones that would work in Excel 2016 or without using Lambda?

[ianand]

from https://chat.openai.com/share/41026af7-7cec-4b41-8de5-349a7df3449f

Text of chat recreated below:

Anonymous Can you convert this formula that works Microsoft Excel 365 into one that does not use lambda and is compatible with Excel 2016:

=LAMBDA(word, MID(word,SEQUENCE(1,LEN(word)),1))

ChatGPT To convert a formula that uses LAMBDA and SEQUENCE, which are not available in Excel 2016, into a version compatible with Excel 2016 or earlier, we have to find an alternative approach since these functions were introduced in Excel 365. The given formula seems to aim at splitting a word into its individual characters in a vertical array.

Without LAMBDA and SEQUENCE, one common approach to achieve a similar result in older Excel versions is to use a combination of other functions within an array formula context. Since Excel 2016 does not support dynamic arrays in the same way as Excel 365, you would typically need to enter this formula in each cell where you want a character to appear or use a helper column approach.

Here’s how you can achieve a similar effect using MID, ROW, and INDIRECT in an array formula. This example assumes you want to split the word in cell A1:

Enter the following formula in the first cell where you want to start the list of characters (e.g., B1): excel

=MID($A$1, ROW(INDIRECT("1:" & LEN($A$1))), 1)

After typing the formula, instead of pressing Enter, press Ctrl + Shift + Enter to enter it as an array formula. Curly braces {} will appear around the formula if you did this correctly, but do not type them manually. Drag the formula down to fill as many cells as the maximum number of characters you expect in your input word. The formula will return each character of the word in each cell. This approach works by using ROW(INDIRECT("1:" & LEN($A$1))) to generate an array of numbers from 1 to the length of the word, simulating the SEQUENCE function's output. MID is then used to extract each character one by one based on these numbers.

Remember, Excel 2016 and earlier versions do not handle dynamic arrays, so you need to fill down the formula to cover the maximum length you expect or use another method to dynamically adjust the range size, which could involve VBA or additional manual steps.

[zkimmel]

FWIW I am encountering the same issue -- ubiquitous #NAME errors in Excel 2021 / Windows 11.

[tlpwka]

FWIW I am encountering the same issue -- ubiquitous #NAME errors in Excel 2021 / Windows 11.

Can confirm, the same with Excel 2019. Seems like only subsciption-based office 365 support lambda function.

[ianand]

Apologies. Many of the named functions in the sheet make use of lambda and its disappointing that this limits the audience for this sheet even further. I genuinely don't know yet the feasibility and priority for migrating the formulas and sheet structure for backward compatibility (esp since I don't have that version of Excel).

[jiacheo]

LibreOffice also has the same errors of "#NAME" and other Errors everywhere.

[physx2494]

I'm getting #NAME on Microsoft® Excel® LTSC MSO (16.0.14332.20649) 64-bit

[ianand]

Update: Some of you were disappointed to find that 'Spreadsheets-are-all-you-need' (my GPT-2 implementation in Excel) only works with Excel 365. If you tried it on an older version and ran into issues, please drop a comment or DM me—I'm working on a possible fix and could use some folks to test it.