CODEWITHSUNDEEP
excelstringexcel-formulaformulaworksheet
user28873684
user28873684

Reputation: 1

Number Extracting Formula works in Blank Worksheet, but not in the needed Workbook

Problem: A formula we built to extract every other number in a string and add the two groups of numbers together, pasting them in a new cell, is working in a Blank Worksheet, but not in the Workbook we need it to work in.

Question: Does anyone know why one function might be working in a blank worksheet, but not in another one? Both cells are blank, no formatting, set to General, manually entered, so there are no other functions that should be pointing to or from it. Otherwise, if it isn't possible, is there another way to rewrite this function to circumvent our issue, read every other number in the string, and add the two separate groups of numbers into 2 results and paste in a cell together ("6 PLT @ 12 FT"). Any ideas or help is appreciated. Thanks.

Working on a function to pull every other number from a cell and add them separately together to display in another cell.

Ex. Transport 4 PLT @ 8 FT Example 2 PLT @ 4 FT

Results: 6 PLT @ 12 FT

Finally got a working formula in a blank worksheet to extract it as a string, ignore the text, and only read the numbers in a string. (Both the Transport line and Example line are in the same cell).

=TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))

Results: 4824

Building off of this, we assigned this function as a variable so it could be applied throughout the rest of the function we built around it to add every other number into 2 separate results.

=LET(N,TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"")),CONCATENATE(SUMPRODUCT(MID(N,ROW(INDIRECT("1:"&LEN(N))),1)*MOD(ROW(INDIRECT("1:"&LEN(N))),2))," @ ",SUMPRODUCT(MID(N,ROW(INDIRECT("1:"&LEN(N))),1)*MOD(ROW(INDIRECT("1:"&LEN(N)))-1,2)),"FT"))

Results: 6 PLT @ 12 FT

Now, for context, my coworker and I are just beginning to work with formulas, so I'm not sure if it could be better or not. Regardless, it works in the blank worksheet we've been using as scratch paper. Excited to get this calculator working, we threw the formula into the worksheet it's going to be applied to and results in "#REF" error. Pulling apart the pieces, we found that out first function, which is supposed to return the string 4824, returns blank if text is before the numbers now, and if we remove the text, it only returns 4, without reading the rest of the numbers. We've always tested it with a string of text before, since we know that's how it would be entered.

The workbook we're trying to use it in has 80+ worksheets with 100 rows each, so we're a little worried the Row function is breaking as a result of excel not being strong enough. We also figured it might be corruption, given the amount of worksheets and how many functions there are in the workbook we're using it in. We tried Open & Repair, but the results were the same. We thought it might be the indirect function giving us trouble, so tried replacing it with:

=TEXTJOIN(, 1, TEXT(MID(A1, ROW($AB$1:INDEX($A$1:$A$1000, LEN(A1))), 1), "#;-#;0;"))

Which still results in 4824 on scratch paper, but the same blank or single digit status as before in the workbook.

Update: I made a copy of the Workbook Sheet and our original formula started to work again. It does look like it is a problem with the workbook itself, and less the coding of the cells. Is there functions that would work better than others with large Workbooks with lots of Worksheets?

Upvotes: 0

Views: 137

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

I cannot explain the behavior difference of your formula in a new workbook vs your exisiting workbook.

However, if your "units" are always in all caps, the "@" is as shown, and irrelevant comments in mixed case, then the following will combine as you show in your output line and be sensitive to whatever units you care to include.

="Result: " &
    LET(
        d, A1,
        split, TEXTSPLIT(d, " "),
        fltr, FILTER(split, EXACT(UPPER(split), split) * (split <> "@")),
        toRWS, WRAPROWS(fltr, 2),
        grp, GROUPBY(CHOOSECOLS(toRWS, 2), --CHOOSECOLS(toRWS, 1), SUM, 0, 0),
        revGrp, CHOOSECOLS(grp, 2, 1),
        TEXTJOIN(" ", , CHOOSEROWS(revGrp, 2), "@", CHOOSEROWS(revGrp, 1))
    )

enter image description here

Upvotes: 0

P.b
P.b

Reputation: 11578

=LET(x,TEXTSPLIT(A1,," "),y,LAMBDA(z,SUM(INDEX(--x,TOCOL(SEQUENCE(ROWS(x),,0)/ISNUMBER(XMATCH(x,z)),2)))),"Result: "&y("PLT")&" PLT @ "&y("FT")&" FT")

Maybe this performs better?

Upvotes: 0

Related Questions