Results 1 to 6 of 6

BYROW(...LAMBDA(...) ) result in #CALC! error

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,084

    BYROW(...LAMBDA(...) ) result in #CALC! error

    Hello,

    As print screen below, I have some text (A1:A3) that I'd like to split.

    The delimiter are ( , / ; | : ).

    I was able to split it using FILTERXML and SUBSTITUTE (as shown in column E to K depending on the number of text). However, when I'd like it to be return the result dynamically both horizontally and vertically, I get #CALC! error.

    Question now is how do I incorporate my dynamic array result horizontally down vertically based on the size of my data.

    My working formula:
    "=TRANSPOSE(FILTERXML(""<M><D>""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"","",""||""),""/"",""||""),"";"",""||""),"":"",""||""),""||"",""</D><D>"")&""</D></M>"",""//D""))"
    Not working formula:
    =BYROW(A1:A3,
    LAMBDA(br, TRANSPOSE(FILTERXML("<M><D>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(br,",","||"),"/","||"),";","||"),":","||"),"||","</D><D>")&"</D></M>","//D")))
    )
    SplitText Dynamically.png
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. ARCANE: LAMBDA function calling XLM functions
    By hrlngrv in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-19-2022, 07:09 PM
  2. [SOLVED] LAMBDA iteration
    By Slabu in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-20-2021, 08:39 PM
  3. Replies: 6
    Last Post: 03-20-2020, 09:26 AM
  4. Lambda Expressions in Excel VBA
    By Gregor y in forum Tips and Tutorials
    Replies: 4
    Last Post: 10-17-2014, 06:59 PM
  5. Replies: 0
    Last Post: 03-20-2013, 10:31 PM
  6. calc result to cell location
    By gavster in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 02-24-2007, 07:59 AM
  7. Calc error
    By Michel Finschi in forum Excel General
    Replies: 2
    Last Post: 01-07-2006, 12:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1