This is a 2 steps request. First, I want to select and input a list of addresses (of values, not text) into a cell like this a3,b5,d55,k20 and have it display exactly like that. Not as a value or a formula. Next in another cell, I need to calculate the MAX value in that list. Do any of you see a simple way to do that? jgb

mtarler Thanks. Good news!!! I found a solution.Kept me up most of the night and after 2 hours testing this morning, got it to work.It is not exactly the way I originally wanted, but it gives the exact same result I wanted in one cell. Originally I wanted to simply type a list of cell refs as text like this A3,T5,G44 then in another cell calculate the Early Start as a max(list)+0 value of that string. The problem was converting that text string into actual cell refs for the MAX() function. Wasn't going to happen. My eureka moment (4am) was to put the cell refs directly into the Early Start cell and calculate the truncated text string in the list cell. In other words vice-versa to the original plan. I had to enter that list anyway, so which cell it went into didn't matter. I ended up with the Early Start cell being the input cell as a MAX() formula.That cell would be =MAX(A3,T5,G44)+1 in cell ES Then the list was a nested text formula of FORMULATEXT(ES) then MID(that,6,100) to strip off the "=MAX(" (100 is arbitrary) then finally =SUBSTITUTE(all that),")+1","",1) to strip off the trailing ")+1". =SUBSTITUTE(MID(FORMULATEXT(ES),6,100),")+1","") and that resulted in converting formula =MAX(A3,T5,G44)+1 into text A3,T5,G44 Now I have to do something similar to calculate the Late Finish, and that's gonna be tougher. Thanks again for the help.jgb

JG_Berson This is pretty simple but does require Excel 365 (and TBH I don't recommend this and would recommend re-evaluating what you are doing and see if there is a 'better' way): =LET(in,TEXTSPLIT(A1,","),REDUCE(0,in,LAMBDA(p,I,MAX(p,INDIRECT(TRIM(I))))))A1 is the text based list of cellsthis assumes min of 0 but if all the numbers might be negative then change the '0' after REDUCE to something like -9.9E99

mtarlerThanks and YIKES!!!I've done some pretty complex formulas in the past, but this is a new one for me.No doubt it would work, but I can't test or use it as I only have EXCEL 2013. I will heed your advice to find a different way to display that list. thanks againjgb

There may be other options available for you. Why list "a3,b5,d55,k20" in a cell and determine the max from those addresses? If you're able to share a sample workbook and your end goal, we may be able to give you some options.

Patrick2788 I agree and suggest as much also.JG_Berson as for more simple formula it turns out INDIRECT might work on an array (it originally failed for me but I think that was because I had to add the TRIM). So here is a more simple formula but still won't work for you:=MAX(INDIRECT(TRIM(TEXTSPLIT(G1,","))))but using the old trick to go from text to array may work for you but you will probably have to enter the formula using CTRL-SHIFT-ENTER:=MAX(INDIRECT(TRIM(MID(SUBSTITUTE(G1,",",REPT(" ",LEN(G1))),(ROW($A$1:INDEX($A:$A,LEN(G1)-LEN(SUBSTITUTE(G1,",",""))+1))-1)*LEN(G1)+1,LEN(G1)))))I'll cross my fingers for you.

Convert list of addresses | Microsoft Community Hub

mtarler
Silver Contributor
Oct 26, 2022
JG_Berson This is pretty simple but does require Excel 365 (and TBH I don't recommend this and would recommend re-evaluating what you are doing and see if there is a 'better' way):

=LET(in,TEXTSPLIT(A1,","),REDUCE(0,in,LAMBDA(p,I,MAX(p,INDIRECT(TRIM(I))))))
A1 is the text based list of cells
this assumes min of 0 but if all the numbers might be negative then change the '0' after REDUCE to something like -9.9E99
- JG_Berson
  Copper Contributor
  Oct 27, 2022
  mtarlerThanks and YIKES!!!
  I've done some pretty complex formulas in the past, but this is a new one for me.
  No doubt it would work, but I can't test or use it as I only have EXCEL 2013.
  
  I will heed your advice to find a different way to display that list.
  
  thanks again
  jgb
  - Patrick2788
    Silver Contributor
    Oct 27, 2022
    There may be other options available for you. Why list "a3,b5,d55,k20" in a cell and determine the max from those addresses? If you're able to share a sample workbook and your end goal, we may be able to give you some options.

Forum Discussion

Convert list of addresses

Share

Resources