Formula explaination

[terrysoper1973]

Could someone please explain this formula. I used it to extract a postal code 7 characters from the left of the last comma. Something like this:
John, Denver 30 prospect street Toronto p0a1j0, ontario

=MID(E2,LOOKUP(2^15,FIND(",",E2,ROW(INDIRECT("1:"&LEN(E2)))))-7,7)

Thanks

Terry

[zbor]

what you want as result from this sentence?

[terrysoper1973]

The target of the formula from the sentence is "p0a1j0"

[Andrew-R]

I can't explain your formula, but I can explain mine that does the same thing (apparently) ... =MID(E2,FIND("~",SUBSTITUTE(E2,",","~",LEN(E2)-LEN(SUBSTITUTE(E2,",",""))))-7,7)

[zbor]

Ot this will extract last word:

=REPLACE(A1,1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),"")

[Andrew-R]

I worked out what the original formula was doing. By funny coincidence it's using a trick I was only taught this afternoon to avoid putting big arrays into formula.

Thing is, I was using it as a joke

[terrysoper1973]

ok your formula is great; could you explain it?

[terrysoper1973]

oh great; could you explain my formula?

[Andrew-R]

OK, your formula is - =MID(E2,LOOKUP(2^15,FIND(",",E2,ROW(INDIRECT("1:"&LEN(E2)))))-7,7)

Working from the inside out:

ROW(INDIRECT("1:" & LEN(E2))) is creating an array of numbers from 1 to the length of the value in E2

The FIND is then using each number in the above array as the start point in the string to find a comma.

LOOKUP is then looking up a very big number in the array of values produced by the FIND, because the number it is looking for is much bigger than anything it's actually going to find it's just returning the largest value, which, by happy co-incidence, will be the location of the last comma.

The MID statement is then extracting a portion of text from E2, starting 7 characters back from the location of the last comma and with a length of 7 characters.

I suppose it's as good a way as any of doing this.

[terrysoper1973]

I kind of understand all but the purpose of row() in the formula. It's possible that I don't correctly understand row(), and indirect() properly.

[daddylonglegs]

.....LOOKUP is then looking up a very big number in the array of values produced by the FIND, because the number it is looking for is much bigger than anything it's actually going to find it's just returning the largest value......

LOOKUP in this form actually returns the last number in the array. In this formula that will always also be the largest value but it might not be in some other situation, e.g. this formula

=LOOKUP(2^15,{"a",15,2,2354,#N/A,"t",1,"e"})

returns the 1 because that's the last number

[Andrew-R]

I kind of understand all but the purpose of row() in the formula. It's possible that I don't correctly understand row(), and indirect() properly.

ROW() and INDIRECT() are just being used to generate a long list of sequential values. It's confusing and slower than using the formula that I posted (which also rocks, because I wrote it )

@Daddylonglegs - I stand corrected, sir.

[daddylonglegs]

This formula

=ROW(A1:A100)

generates an array of values 1 to 100. You can see that by doing this:

Paste that formula into any cell and select that cell
Put the cursor in the formula bar and press F9 - you should now see that array

If you knew that your data in E2 would be max 100 characters then you could use that instead, e.g.

=MID(E2,LOOKUP(2^15,FIND(",",E2,ROW(A1:A100)))-7,7)

It also turns out that this version of the ROW formula generates the same array

=ROW(1:100)

....but if the cell length is variable we can construct the same thing using a formula that generates the cell length, i.e.

=ROW("1:"&LEN(E2))

That constructs a text string inside ROW function like this

ROW("1:55")

but that text isn't read as a reference .....INDIRECT can be used because it converts a text reference to a TRUE reference.....so that becomes

=ROW(INDIRECT("1:"&LEN(E2)))

In fact even with this version where the rows are fixed

=MID(E2,LOOKUP(2^15,FIND(",",E2,ROW(A1:A100)))-7,7)

if you copy that formula down a column....or insert or delete rows the ROW function might change and make the formula invalid...so INDIRECT also "fixes" the row values so they won't be changed by row deletion etc. hence this version

=MID(E2,LOOKUP(2^15,FIND(",",E2,ROW(INDIRECT("1:100"))))-7,7)

[terrysoper1973]

Well Andrew, if you can spare 5 more minutes to explain your formula, it would really help me get a handle on this.

Your formula does rock. But be careful; I think I got that formula from DLL. I know I got something helpful from him. In another post.

Thanks you so much; I really am thankful.

[terrysoper1973]

Oh thanks DLL, I doubled messages a bit there.

[terrysoper1973]

Hey that f9 thing helped me. When I was testing, I was just getting a 1 and not realizing there was an array there. Not that I have much experience with any of this. I learn more from all of you faster than a day of tutorial searching.

Thanks Guys!

[Andrew-R]

OK, my formula is =MID(E2,FIND("~",SUBSTITUTE(E2,",","~",LEN(E2)-LEN(SUBSTITUTE(E2,",",""))))-7,7)


Again working from the outside in:

SUBSTITUTE(E2,",","") replaces all of the commas in E2 with empty strings, so...

LEN(E2)-LEN(SUBSTITUTE(E2,",","")) subtracts the length of E2 without any commas from the length of E2 with the commas in. This will therefore return the number of commas in E2.

The next SUBSTITUTE replaces the last comma in E2 with a tilde (~), using the formula bit above to know which number comma it has to replace. The tilde has been picked just because it's an unusual character which is unlikely to appear in the text.

The FIND looks for the tilde in this modified string, which gives us the position that was previous occupied by the final comma.

From there on it's the same as your formula - a sub-string of E2 is produced by MID, starting 7 characters in from where the tilde is and with a length of 7 characters.

My formula is slightly faster, but will throw a spanner if there just happens to be a tilde in the original string. So, it's really just a case of which you prefer.

[terrysoper1973]

Both formulas are elegant and full of knowledge for me to learn. You've all helped me go from
substitute(Terry,"0","1"), with a Row(1:2^15) to go.

Thanks Guys; your the best!

[Andrew-R]

Glad we helped out. Do remember to mark this thread as solved and <cough> there's always the scales icons on our posts if you feel you'd like to thank us