tag:blogger.com,1999:blog-11238774.post251264546289871148..comments2024-03-11T05:16:11.252-07:00Comments on Excel_Geek...I'll Do That in Excel for $50: Pull numeric value from stringExcel_Geekhttp://www.blogger.com/profile/15299063299312360136noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-11238774.post-21170577736130228332009-10-15T09:42:59.045-07:002009-10-15T09:42:59.045-07:00No VBA. You are a hero and deserve more than $50.No VBA. You are a hero and deserve more than $50.Garyhttps://www.blogger.com/profile/16665094681845048584noreply@blogger.comtag:blogger.com,1999:blog-11238774.post-65631079894741507802009-10-15T06:24:33.038-07:002009-10-15T06:24:33.038-07:00Funky,
You're dead on. I forgot to mention an...Funky,<br /><br />You're dead on. I forgot to mention another constraint: no VBA. :( <br /><br />I would have certainly taken a RegEx approach similar to what you're describing. <br /><br />Thanks for the comment.Excel_Geekhttps://www.blogger.com/profile/15299063299312360136noreply@blogger.comtag:blogger.com,1999:blog-11238774.post-83759957826343907182009-10-15T06:14:07.615-07:002009-10-15T06:14:07.615-07:00I run into this all the time, but Excel's stri...I run into this all the time, but Excel's string-manipulation is a pain in this regard. It is however tailor-made for regular expressions.<br /><br />My approach is to cut the column in question, paste it into TextPad, and use a regular expression, in this case:<br />.* \([0-9]*\)ft.*$ - search & replace with the replacement expression \1 and you're done.<br /><br />Since apparently you can add RegExes to VBA applications http://lispy.wordpress.com/2008/10/17/using-regex-functions-in-excel/ maybe a user-defined generic regex function is in order, so you can use the formula neatly?Garyhttps://www.blogger.com/profile/16665094681845048584noreply@blogger.com