domstyle.net
the bird is the word!
$1.75 US
$1.95 CAN

Login 

FAQ     Search

 
It is currently Sat Nov 25, 2017 1:37 am

All times are UTC - 6 hours




Post new topic Reply to topic  [ 22 posts ]  Go to page Previous  1, 2
Author Message
 Post subject: Re: Excel Formula
PostPosted: Tue Sep 09, 2008 1:56 pm 
Offline
Reprobate, Heretic, Infidel, Heathen, Apostate, Skeptic, Profaner, Mr.Antichrist, Coot
User avatar

Joined: Tue Feb 03, 2004 3:56 pm
Posts: 3008
Geek wrote:
=SUM(VALUE(LEFT(TEXT(0,C7), FIND("(",TEXT(0,C7)&"(")-1)), VALUE(LEFT(TEXT(0,D7), FIND("(",TEXT(0,D7)&"(")-1)), VALUE(LEFT(TEXT(0,E7), FIND("(",TEXT(0,E7)&"(")-1)))


dom wrote:
Code:
=VALUE(LEFT(CONCATENATE(TRIM(D7), " "), FIND(" ", CONCATENATE(TRIM(D7), " "))-1))


hmm... these are awfully close... obviously one is searching for a space and the other a (, and mine is a sum of three of these. I only concatenate in one place, while dom does it in two... I should probably change that. I'll see if this helps when next I go into work. Other than that they're basically identical except for me using & instead of "concatenate".

_________________
ImageStudentImage


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 10, 2008 8:26 pm 
Offline
i do what i want
User avatar

Joined: Sun Feb 01, 2004 7:36 pm
Posts: 4416
Location: Buttermilk Biscuits
i'm not surprised that they're so similar. i wouldn't be surprised if we ended up w/ the same thing lol. anyways, i was just trying to figure out what the error you were getting was

you know, this would be much easier if excel had a function for regular expressions

_________________
:dance: dom smash! :dance:


Top
 Profile  
 
 Post subject:
PostPosted: Thu Sep 11, 2008 8:12 am 
Offline
Reprobate, Heretic, Infidel, Heathen, Apostate, Skeptic, Profaner, Mr.Antichrist, Coot
User avatar

Joined: Tue Feb 03, 2004 3:56 pm
Posts: 3008
Regular expressions are pretty sweet. Never actually used them, but had them in a class.

_________________
ImageStudentImage


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 12, 2008 8:33 am 
Offline
Reprobate, Heretic, Infidel, Heathen, Apostate, Skeptic, Profaner, Mr.Antichrist, Coot
User avatar

Joined: Tue Feb 03, 2004 3:56 pm
Posts: 3008
Yeah, I think it's just some kind of mac fuck up. The fact that it works fine except when certain characters are in the string just smells like a bug. The function that's fucking up is the FIND function. Doesn't like those d's and s's. Weird.

_________________
ImageStudentImage


Top
 Profile  
 
 Post subject: Re: Excel Formula
PostPosted: Thu Jun 25, 2009 10:15 pm 
Offline
i do what i want
User avatar

Joined: Sun Feb 01, 2004 7:36 pm
Posts: 4416
Location: Buttermilk Biscuits
using regular expressions in excel came up at work today. in excel, open the vb editor, plop this bad boy in. hit Tools>References, and put in a check in the box by "Microsoft VBScript Regular Expressions".

here's what i needed at work:
Code:
Function regexReplace(findIn, pattern, replaceWith, Optional replaceAll = True, Optional ignoreCase = True)
   Dim re As regexp
   Set re = New regexp

   re.pattern = pattern
   re.Global = replaceAll
   re.ignoreCase = ignoreCase
   re.MultiLine = True

   regexReplace = re.replace(findIn, replaceWith)
End Function


here's what your spreadsheet probably could've used (untested):
Code:
Function regexFindFirst(findIn, pattern, replaceWith, Optional ignoreCase = True)
   Dim regex As RegExp
   Dim matches As MatchCollection

   Dim myMatch As Match

   Set regex = New RegExp
   regex.IgnoreCase = ignoreCase
   regex.Global = False
   regex.Pattern = pattern
   Set matches = regex.Execute(findIn)
   
   If matches.Count > 0 then regexFindFirst = matches(0).value
End Function


this should (greedily) find the first string of digits. plop it into any cell in your spreadsheet
Code:
=regexFindFirst(D7, "[0-9]*")

_________________
:dance: dom smash! :dance:


Top
 Profile  
 
 Post subject: Re: Excel Formula
PostPosted: Fri Jun 26, 2009 2:46 pm 
Offline
Reprobate, Heretic, Infidel, Heathen, Apostate, Skeptic, Profaner, Mr.Antichrist, Coot
User avatar

Joined: Tue Feb 03, 2004 3:56 pm
Posts: 3008
Thanks for the help, but I gave up on it a while ago and I'm not gonna go back and add that stuff.

_________________
ImageStudentImage


Top
 Profile  
 
 Post subject: Re: Excel Formula
PostPosted: Sat Jun 27, 2009 2:10 pm 
Offline
i do what i want
User avatar

Joined: Sun Feb 01, 2004 7:36 pm
Posts: 4416
Location: Buttermilk Biscuits
Geek wrote:
Thanks for the help, but I gave up on it a while ago and I'm not gonna go back and add that stuff.


i figured. for posterity then

_________________
:dance: dom smash! :dance:


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 22 posts ]  Go to page Previous  1, 2

All times are UTC - 6 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group