Dim lr, i, a, b
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lr
a = Cells(i, "A").Formula
If Left(a, 3) = "=IF" Then
a = Mid(a, 5, Len(a))
a = Split(Left(a, Len(a) - 1), ",")
For b = 0 To 2
Cells(i, b + 2) = a(b)
Thanks, but I'm not sure that'll work in cases where I might have nested IFs or embedded commas.
I'm writing a macro to validate the formulas in a spreadsheet by locating any division statements and testing to make sure these cannot generate a #DIV/0 error. For example if I find A/X, then I modify the formula to =IF(X,A/X,0). The problem is when I find the "/" in an IF statement or if I have already fixed the code like the example above. I don't want to fix it again as it no longer can generate a #DIV/0 error. I only need to parse out the highest level IF, but what could be inside that IF could be almost anything.
So the character-by-character processing has to track commas, parenthesis, and quotes and the sequence in which they occur to locate the correct comma that delineates the statement. I'm not looking forward to writing that unless I have to...
I'm not sure how you'd display this but it might be possible to call the routine on itself (recursion) for each of the 3 separate elements to pick up nested ifs. The code would need a bit of a rejig and a lot depends on what you want to do with the results.