I love spreadsheets. I loved Visicalc in 1982. Then I loved Lotus 123. Then I really fell in love with Quattro Pro (the first spreadsheet with page tabs). Next I fell in love with Excel. Right now I am enamored with Google Spreadsheets, mainly from the ability to share a spreadsheet live anywhere in the world over the Internet.

Remember slash worksheet range erase? I do. I don't know why but Lotus 123 has stuck in my head for the last 28 years. There's nothing like the =Sum formula or my new favorites =Find and =Substitute. I don't know why I'm sharing this with you, but I must be really bored. Perhaps it's BIM related and a subliminal message to buy Ideate BIMlink for Revit.

I really just wanted to show off ths formula I made in Excel. It finds spaces in a sentence, counts the number of letters in the sentence and gives me the last part of the sentence in a new cell.

It's my love of manipulating data and information that makes this so much fun. Can you imagine using a piece of general ledger paper or punch cards for this? Technology gives us more free time so we can waste it on other unnecessary crap in our lives.

=Lookup

=If

=Find

Lots of cool formulas to make use of all of the information you've been collecting.

/Blogpost End

=RIGHT(F2,LEN(F2)-(H2+IF(I2<>"",(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)-H2,0)+IF((IF(IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=H2,"",IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))))<>"",(IF(IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=H2,"",IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))))-I2,0)+IF((IF(IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=H2,"",IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))))+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=H2,"",IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))))+1))=(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1),"",IF(ISERROR(FIND(" ",$F2,J2+1))=TRUE,"",FIND(" ",$F2,J2+1))))<>"",(IF(IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=H2,"",IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))))+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,I2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,I2+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))))+1))=I2,"",IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,I2+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=H2,"",IF(ISERROR(FIND(" ",$F2,I2+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))))+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,I2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))))+1))))-(IF(IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=H2,"",IF(ISERROR(FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1))=TRUE,"",FIND(" ",$F2,(IF(IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1))=H2,"",IF(ISERROR(FIND(" ",$F2,H2+1))=TRUE,"",FIND(" ",$F2,H2+1)+1)))),0)))

## 2 comments:

Now imagine if you could export every steel beam from Revit into a spreadsheet as:

Name | Length

You could use a formula similar to that, but have it find the "w22x12" and then extract your weight value, multiply it by the length, and bam! Excel is calculating steel weight.

Or leave your steel in Revit and create a calculated value that multiples Length X w/lf and get that right in a schedule. I love schedules and I love the possibilities of BIMlink

Post a Comment