Find nth instance of a character in a string

I'm sure I've done this in the past but for the life of me I can't remember 
it now.

Say I have a string "http://www.theexceladdict.com/tutorials.htm" in cell 
A1. I want to determine the position of the last "/" (forward slash). The 
strings won't always contain the same # of "/"s.

I need to be able to do this as a formula and also in VBA code.

I appreciate your help.

-- 
Have a great day,
Francis Hayes (The Excel Addict)

http://www.TheExcelAddict.com
Helping Average Spreadsheet Users 
Become Local Spreadsheet Experts
0
1/20/2005 8:07:01 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
821 Views

Similar Articles

[PageSpeed] 50

If you want to extract what's to the right of the last forward slash you can 
use

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

if you want the position

=FIND("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))


Regards,

Peo Sjoblom

"Francis Hayes (The Excel Addict)" wrote:

> I'm sure I've done this in the past but for the life of me I can't remember 
> it now.
> 
> Say I have a string "http://www.theexceladdict.com/tutorials.htm" in cell 
> A1. I want to determine the position of the last "/" (forward slash). The 
> strings won't always contain the same # of "/"s.
> 
> I need to be able to do this as a formula and also in VBA code.
> 
> I appreciate your help.
> 
> -- 
> Have a great day,
> Francis Hayes (The Excel Addict)
> 
> http://www.TheExcelAddict.com
> Helping Average Spreadsheet Users 
> Become Local Spreadsheet Experts
0
PeoSjoblom (789)
1/20/2005 8:31:10 PM
Francis,

And the following gets you the position using code ...
'-----------------------------------------------------------------------------------
Function LastPosition(ByVal strInput As String, ByVal strChars As String) As Long
 'Jim Cone - San Francisco - Sep 18, 2003
 'ByVal allows variants to be used for the string variables
  On Error GoTo WrongPosition
  Dim lngPos    As Long
  Dim lngCnt    As Long
  Dim lngLength As Long
  
  lngPos = 1
  lngLength = Len(strChars)
  
  Do
    lngPos = InStr(lngPos, strInput, strChars, vbTextCompare)
    If lngPos Then
       lngCnt = lngPos
       lngPos = lngPos + lngLength
    End If
  Loop While lngPos > 0
  LastPosition = lngCnt
  Exit Function
  
WrongPosition:
  Beep
  LastPosition = 0
End Function

'Call it like this...
Sub WhereIsIt()
Dim N As Long
N = LastPosition("http://www.theexceladdict.com/tutorials.htm", "/")
MsgBox N
End Sub
'----------------------------------------

Regards,
Jim Cone
San Francisco, USA


"Francis Hayes (The Excel Addict)" 
<TheExcelAddict@discussions.microsoft.com> wrote in message 
news:CC13B674-BAE9-485E-931D-016FE614960E@microsoft.com...
> I'm sure I've done this in the past but for the life of me I can't remember 
> it now.
> Say I have a string "http://www.theexceladdict.com/tutorials.htm" in cell 
> A1. I want to determine the position of the last "/" (forward slash). The 
> strings won't always contain the same # of "/"s.
> I need to be able to do this as a formula and also in VBA code.
> I appreciate your help.
> -- 
> Have a great day,
> Francis Hayes (The Excel Addict)
> http://www.TheExcelAddict.com
> Helping Average Spreadsheet Users 
> Become Local Spreadsheet Experts
0
jim.coneXXX (771)
1/20/2005 9:09:07 PM
Peo Sjoblom wrote...
>If you want to extract what's to the right of the last forward slash
you can
>use
>
>=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

You could also use MID and dispense with one of the LEN calls.

=MID(A1,FIND(CHAR(127),SUBSTITUTE(A1,"/",CHAR(127),
LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,1024)

Alternatively, using a defined name like seq referring to
=ROW(INDIRECT("1:1024")), this could be done with the array formula

=MID(A1,MAX(IF(MID(A1.seq,1)="/",seq))+1,1024)

>if you want the position
>
>=FIND("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))
....

Using seq as above, this could be given by the array formula
=MAX(IF(MID(A1.seq,1)="/",seq))

0
hrlngrv (1990)
1/21/2005 12:07:26 AM
Jim Cone wrote...
>And the following gets you the position using code ...
>Function LastPosition(ByVal strInput As String, ByVal strChars As
String) As Long
....
>'Call it like this...
>Sub WhereIsIt()
>Dim N As Long
>N = LastPosition("http://www.theexceladdict.com/tutorials.htm", "/")
>MsgBox N
>End Sub
....

If one uses Excel 2000 or later, why this rather than a simple wrapper
around the InStrRev VBA6 function? If one uses Excel 5/95 or 97, why
not keep it simple?


Function foo(s As String, ss As String) As Long
Dim k As Long, n As Long

k = Len(ss)
n = InStr(1, s, ss)

If n > 0 Then
foo = Len(s) - k

Do
foo = foo - 1
Loop Until Mid(s, foo, k) = ss Or foo <= n
Else
    foo = n

  End If

End Function

0
hrlngrv (1990)
1/21/2005 12:19:46 AM
Hi Harlan,

I quickly ran three speed tests using Timer on
l00,000 loops on the string provide by Francis..
For five trials the average time was

Function Foo:  0.92 seconds
Function LastPosition:  0.83 seconds  
Function RevInStr:  0.65 seconds   (using 99 as lngStart)

The RevInStr function also comes from my private library :
'----------------------------------------------------------------
' Searches for a character, but starting at the end of the string.
' strString is the string you want to search in
' strChar is the character or string of characters you want to search for
' lngStart is the position in TheString you want to start the search at.
'----------------------------------------------------------------
 Function RevInStr(ByRef strString As String, ByRef strChar As String, _
                   ByVal lngStart As Long) As Long

 Dim lngNdx    As Long
 Dim lngLength As Long

     lngLength = Len(strChar)
    'If strChar length > 1 this reduces number of loops required
     If lngStart <= 0 or lngStart > Len(strString) Then _
        lngStart = Len(strString)- lnglength + 1

     For lngNdx  = lngStart To 1 Step -1
         If Mid$(strString, lngNdx , lngLength) = strChar Then
            RevInStr = lngNdx -1 ' or (lngNdx + lngLength) depending on which section you want
            Exit For
         End If
     Next 'lngNdx 
'    In case nothing found or In case position found was 1 which would return 0.
     If RevInStr = 0 then RevInStr = 1 
 End Function
'-------------------------------------------------

Have I overlooked something?

Regards,
Jim Cone
San Francisco, USA


"Harlan Grove" <hrlngrv@aol.com> wrote in message
 news:1106266786.285550.307270@c13g2000cwb.googlegroups.com...
> Jim Cone wrote...
> >And the following gets you the position using code ...
> >Function LastPosition(ByVal strInput As String, ByVal strChars As
> String) As Long
> ...
> >'Call it like this...
> >Sub WhereIsIt()
> >Dim N As Long
> >N = LastPosition("http://www.theexceladdict.com/tutorials.htm", "/")
> >MsgBox N
> >End Sub
> If one uses Excel 2000 or later, why this rather than a simple wrapper
> around the InStrRev VBA6 function? If one uses Excel 5/95 or 97, why
> not keep it simple?

> Function foo(s As String, ss As String) As Long
> Dim k As Long, n As Long
> k = Len(ss)
> n = InStr(1, s, ss)
> If n > 0 Then
> foo = Len(s) - k
> Do
> foo = foo - 1
> Loop Until Mid(s, foo, k) = ss Or foo <= n
> Else
>     foo = n
>   End If
> End Function

0
jim.coneXXX (771)
1/21/2005 1:16:22 AM
"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote...
>I quickly ran three speed tests using Timer on
>l00,000 loops on the string provide by Francis..
>For five trials the average time was
>
>Function Foo:  0.92 seconds
>Function LastPosition:  0.83 seconds
>Function RevInStr:  0.65 seconds   (using 99 as lngStart)
....

Not my results.

Redirecting the output of the console command

dir c:\ /s/b

to a text file and loading that text file into Excel 2000 without parsing, I
used the first 40,000 filenames and iterated over them 10 times, so 400,000
calls for each function.

Here are my results. For me, foo is much faster than LastPosition.

------------------------------
foo           10.028
foo2           8.022
LastPosition  34.094
RevInStr       6.017
InStrRev       1.003
findrev        1.003
==============================


And here's my testing module.

'---------------------------------------------------------------------
Sub testem()
  Const MAXITER As Long = 10, NUMROWS As Long = 40000

  Dim r As Range
  Dim s() As String, p() As Long
  Dim i As Long, j As Long
  Dim dt As Date, et As Date

  On Error GoTo ExitProc
  Application.Calculation = xlCalculationManual

  Set r = ActiveSheet.Range("A1").Resize(NUMROWS, 1)

  ReDim s(1 To NUMROWS)
  ReDim p(1 To NUMROWS, 1 To 1)

  For i = 1 To NUMROWS
    s(i) = r.Cells(i, 1).Value
  Next i

  Debug.Print String(30, "-")

  dt = Now
  For i = 1 To MAXITER
    For j = 1 To NUMROWS
      p(j, 1) = foo(s(j), "\")
    Next j
  Next i
  et = Now - dt
  Debug.Print Format(et * 86640#, """foo           ""0.000")

  r.Offset(0, 2).Value = p

  Erase p
  ReDim p(1 To NUMROWS, 1 To 1)

  dt = Now
  For i = 1 To MAXITER
    For j = 1 To NUMROWS
      p(j, 1) = foo2(s(j), "\")
    Next j
  Next i
  et = Now - dt
  Debug.Print Format(et * 86640#, """foo2          "" 0.000")

  r.Offset(0, 4).Value = p

  Erase p
  ReDim p(1 To NUMROWS, 1 To 1)

  dt = Now
  For i = 1 To MAXITER
    For j = 1 To NUMROWS
      p(j, 1) = LastPosition(s(j), "\")
    Next j
  Next i
  et = Now - dt
  Debug.Print Format(et * 86640#, """LastPosition  ""0.000")

  r.Offset(0, 6).Value = p

  Erase p
  ReDim p(1 To NUMROWS, 1 To 1)

  dt = Now
  For i = 1 To MAXITER
    For j = 1 To NUMROWS
      p(j, 1) = RevInStr(s(j), "\", 0)
    Next j
  Next i
  et = Now - dt
  Debug.Print Format(et * 86640#, """RevInStr      "" 0.000")

  r.Offset(0, 8).Value = p

  Erase p
  ReDim p(1 To NUMROWS, 1 To 1)

  dt = Now
  For i = 1 To MAXITER
    For j = 1 To NUMROWS
      p(j, 1) = InStrRev(s(j), "\")
    Next j
  Next i
  et = Now - dt
  Debug.Print Format(et * 86640#, """InStrRev      "" 0.000")

  r.Offset(0, 10).Value = p

  Erase p
  ReDim p(1 To NUMROWS, 1 To 1)

  dt = Now
  For i = 1 To MAXITER
    For j = 1 To NUMROWS
      p(j, 1) = findrev("\", s(j))
    Next j
  Next i
  et = Now - dt
  Debug.Print Format(et * 86640#, """findrev       "" 0.000")

  r.Offset(0, 12).Value = p

  Debug.Print String(30, "=")

ExitProc:
  Application.Calculation = xlCalculationAutomatic
  Application.Calculate
End Sub


Function foo(s As String, ss As String) As Long
  Dim k As Long, n As Long

  k = Len(ss)
  n = InStr(1, s, ss)

  If n > 0 Then
    foo = Len(s) - k

    Do
      foo = foo - 1
    Loop Until Mid(s, foo, k) = ss Or foo <= n
  Else
    foo = n

  End If

End Function


Function foo2(s As String, ss As String) As Long
  Dim k As Long, n As Long, p As Long

  k = Len(ss)
  n = Len(s) - k + 1

  For p = n To 0 Step -1
    If p > 0 Then If Mid(s, p, k) = ss Then Exit For
  Next p

  foo2 = p
End Function


Function LastPosition( _
 ByRef strInput As String, _
 ByRef strChars As String _
) As Long
 'Jim Cone - San Francisco - Sep 18, 2003
 'ByVal allows variants to be used for the string variables
  On Error GoTo WrongPosition
  Dim lngPos    As Long
  Dim lngCnt    As Long
  Dim lngLength As Long

  lngPos = 1
  lngLength = Len(strChars)

  Do
    lngPos = InStr(lngPos, strInput, strChars, vbTextCompare)
    If lngPos Then
       lngCnt = lngPos
       lngPos = lngPos + lngLength
    End If
  Loop While lngPos > 0
  LastPosition = lngCnt
  Exit Function

WrongPosition:
  Beep
  LastPosition = 0
End Function


'----------------------------------------------------------------
' Searches for a character, but starting at the end of the string.
' strString is the string you want to search in
' strChar is the character or string of characters you want to search for
' lngStart is the position in TheString you want to start the search at.
'----------------------------------------------------------------
Function RevInStr( _
 ByRef strString As String, _
 ByRef strChar As String, _
 ByVal lngStart As Long _
) As Long

 Dim lngNdx    As Long
 Dim lngLength As Long

     lngLength = Len(strChar)
    'If strChar length > 1 this reduces number of loops required
     If lngStart <= 0 Or lngStart > Len(strString) Then _
        lngStart = Len(strString) - lngLength + 1

     For lngNdx = lngStart To 1 Step -1
         If Mid$(strString, lngNdx, lngLength) = strChar Then
            RevInStr = lngNdx - 1
           'or (lngNdx + lngLength) depending on which section
           'you want
            Exit For
         End If
     Next 'lngNdx
'    In case nothing found or In case position found was 1 which
'    would return 0.
     If RevInStr = 0 Then RevInStr = 1
End Function


Function findrev(ss As String, s As String) As Long
    findrev = InStrRev(s, ss)
End Function
'---------------------------------------------------------------------


Your RevInStr returns 1 less than all the other functions. I haven't
explored what might be needed to have it return the same values when there
are matches and 0 when there aren't. Returning 1 for both no match at all
and the only match at the beginning of strString is bad programming.

However, given the times for direct InStrRev and findrev, a simple wrapper
around InStrRev, I'll strengthen with my original statement to this: anyone
with VBA6 would a fool not to use VBA6's InStrRev.


0
hrlngrv (1990)
1/21/2005 8:29:05 AM
Harlan,

I used your testing module on the 4700 files in
my Program Files folder.  
The full file path was used for each file.
I ran the test 100 times for a total of 470,000 calls.  
I repeated each set 5 times and the results are...

      Name Time 
      LastPosition Average 7.4202 
      foo Average 4.6128 
      foo2 Average 4.6128 
      RevInStr Average 3.4092 
      findrev Average 0.6018 
      InStrRev Average 0.6018 


Regards,
Jim Cone
San Francisco, USA


"Harlan Grove" <hrlngrv@aol.com> wrote in message 
news:uxO8UN5$EHA.3120@TK2MSFTNGP12.phx.gbl...
> "Jim Cone" <jim.coneXXX@rcn.comXXX> wrote...
> >I quickly ran three speed tests using Timer on
> >l00,000 loops on the string provide by Francis..
> >For five trials the average time was
> >
> >Function Foo:  0.92 seconds
> >Function LastPosition:  0.83 seconds
> >Function RevInStr:  0.65 seconds   (using 99 as lngStart)
> 
> Not my results.
> 
> Redirecting the output of the console command
> 
> dir c:\ /s/b
> 
> to a text file and loading that text file into Excel 2000 without parsing, I
> used the first 40,000 filenames and iterated over them 10 times, so 400,000
> calls for each function.
> 
> Here are my results. For me, foo is much faster than LastPosition.
> ------------------------------
> foo           10.028
> foo2           8.022
> LastPosition  34.094
> RevInStr       6.017
> InStrRev       1.003
> findrev        1.003
> ==============================
-snip-
0
jim.coneXXX (771)
1/21/2005 3:44:48 PM
Reply:

Similar Artilces:

Find and replace 03-04-10
I'm trying to scan a field in one of my tables and find a specific character and remove it. However, the character is a " so I'm having difficulty. The field I speak of of contains the sizes of our material so the values look like this: 1/4" 1/2" 1/3" and so on. How can I find all of the " in my feild and remove them? I don't want to replace them I just want to remove them. Thanks, Chris Savedge Create a query, and in the Criteria row under the problem field, enter: Like "*[""]*" -- Allen Browne - ...

How can I find career change resumes templates?
I like to review some nice "Professional Career Change Resume Templates" for the job market. If someone could tell me where to go on the inter-net, it will be well appreciated. Thanks! You could start at www.google.com "Mike" <Mike@discussions.microsoft.com> wrote in message news:705F99E5-1326-4E81-B98F-A7337C5C9AEA@microsoft.com... :I like to review some nice "Professional Career Change Resume Templates" for : the job market. If someone could tell me where to go on the inter-net, it : will be well appreciated. Thanks! Templates are...

non latin characters in nvarchar
Hi NG, is there a way to select nvarchar fields, that have non latin characters ? Do I have to use UNICODE() and search for high numbers ? Regards Volker Jordan Volker Jordan (v_jordan@web.de) writes: > is there a way to select nvarchar fields, that have non latin characters ? > > Do I have to use UNICODE() and search for high numbers ? That or an expression with patindex: SELECT ... FROM tbl WHERE patindex('%[' + nchar(nnn) + '-' + nchar(65535) + ']%', col COLLATE Latin1_General_BIN2) This assumes that there i...

Lookup/Find help
Windows XP Professional Office 2000 Hypothetical, but hopefully you'll get the gist of it: I have two worksheets. On worksheet #1, I have two columns. First column is a list of entire workgroup by name and 2nd column is the hours worked. On the second worksheet I simply have an list of names that is a subgroup of those on the first page. These indicate a target group. EXAMPLE Worksheet #1 Sam 35 Joe 37 Mary 20 Beth 41 Ted 38 Worksheet #2 Joe Beth Now, on the first worksheet, I want to add a third column for summing only the target workgroup. Basically, I need a function...

check instance running
My application will call another application and would like check is it running or not. Are there any function to check the instance already running? Your information is great apprecaited, Check http://www.mvps.org/access/api/api0007.htm at "The Access Web" -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "iccsi" <inungh@gmail.com> wrote in message news:d755e8d9-8e1b-414f-a115-ae450d48075f@y17g2000yqd.googlegroups.com... > My application will call another application and would like check is > i...

how to organize several class and their instance in windows programming
Hi, everyone, I'm designing a project which could do some image processing, motion control and networking using MFC. nowadays I'm puzzled how to organize so many class and their instances. Though I think the "design pattern" could solve my problem, but what I only want is to get a simple method or a belief understand. suppose these class are like this: CImageProc (class deal with image processing) CMotionControl (class deal with motion control) CNetwork (class deal with networking) CXXXClass (class doing other work) This is the way I use, in the CMainFrame( I'm...

Create an instance of new Customized entity by Workflow
I created an entity named E1 under Account entity(Many-to-1 relationship with Account) then I want to create a workflow Rule that after creating an Account an instance of E1 entity is created automatically. It seems Activity creation is possible in workflow automatically! Is any soloution for this issiue? -- Mohsen Ahmadi you can create activities but not custom entities. You would need to either do this by using a callout or by creating a workflow .net assembly that can create the custom entity for you. ======================= John O'Donnell Microsoft CRM MVP http://codegallery.g...

Multiple Outlook 2003 instances launched
I'm running Outlook 2003 on my home machine to access my Verizon email account. It does not matter how I launch Outlook, from the Start Menu, from a Desktop shortcut, or from the Launch Bar, I always get multiple instances of Outlook running. Does anyone know what is going on here and how I can prevent the multiple launch? TIA Jim Outlook will only display one instance under normal circumstances. Did you perhaps in a previous session elect to open other folders in a new window? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Al...

can't find normal.dot
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello, I need to delete my normal.dot file and I can't find it anywhere. I've had to do this before and I was able to find it in the templates folder, but I guess an update or two has changed that. Can anyone tell me where to find it? Spotlight says that it doesn't exist. But I have to be able to delete it. Is it called something different now? If you're using Spotlight & searching for "Normal.dot" it's no wonder it isn't being found - that isn't the correct name :-) it's ...

Finding maximum value
Hello, I want to find the maximum value in a column, but I want to ignore the negative and positive part of the value. Basically, the maximum difference from zero. Example: the maximum value I am looking for will be -0.467. -0.467 0.345 -0.253 0.411 Thanks Ruan One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =INDEX(A1:A4,MATCH(MAX(ABS(A1:A4)),ABS(A1:A4),FALSE)) In article <OGKhnF1KEHA.2100@TK2MSFTNGP10.phx.gbl>, "Ruan" <ruan@aegismed.com> wrote: > Hello, > > I want to find the maximum value in a column, but I want to ignore the > negati...

How do I find the inside page in the 3-panel brochure.
I am trying to make a 3-panel brochure and all that seems to be available is the side shown in the brochure options. When I print, it prints both sides yet I can see no way to access the inside part. Thanks. View, status bar... this will allow you to change page views, you will see the page tabs at the bottom of the screen. I am not sure I know what you are asking. Are you saying you are printing the brochure sight unseen? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "springmtn" <springmtn@discussions.microsoft.co...

Find a multiple photos on a page template for Publisher?
Possible to find a template for Publisher to print a page full of multiple photos without recreating the page each time I print. Why not save your page you have created as a template? File, "save as", files of type, scroll down to Publisher template. -- Mary Sauer http://msauer.mvps.org/ "srfaith" <srfaith@discussions.microsoft.com> wrote in message news:157E95AB-1159-4889-B196-61E147E6E240@microsoft.com... > Possible to find a template for Publisher to print a page full of multiple > photos without recreating the page each time I print. ...

Searching in a column of strings
Hi I try to find how many cells have a sequence of charcters. From Cell A1:A50. Exemple for "NY" : cellA1 = NY123 cellA2 = NY3235 cellA3 = NY5434 cellA4 = LA4234 function(NY) = 3 Like Seach but instead to be in a single cell, it's in a sequence of cell? Thanks Jack Jack =COUNTIF($A:$A,"*NY*") Regards Trevor "Jack" <anonymous@discussions.microsoft.com> wrote in message news:348501c3fd71$700c6c60$a001280a@phx.gbl... > > Hi > > I try to find how many cells have a sequence of > charcters. From Cell A1:A50. > > Exe...

formatting selected characters or numbers in each cell within a range of cells
I am sending this to several Excel newsgroups, because I am not sure which one if the most appropriate. I apologize in advance for the multiple postings. I have MS Excel 2000 (version 9.0.3821 SR-1). How can I format only a selected character or number within each cell in a specific range of cells? For instance if I have a cell containing �1234234�, how can I format it so that only the 2�s are bold or are colored? Obviously, I have a range of cells, and I want to format all the 2�s occurring in any of the cells as either bold or colored. Conditional formatting does not seem to all...

Finding Desktop region in MFC
I am working on a commercial application, and recently discovered a bug I have been asked to fix (they threw it back in my lap). Run the application on a 2-monitor system, such as a laptop in a dock with an external monitor. Drag the main window to the second (non-taskbar) window and quit. Now move the laptop to a different dock with the second menu on the other side (or without a second monitor at all) and launch the app. Oops. The app continues to want to go on the side it was last put, even though there is now no monitor there. (Yes, my dock in the office and at home have the second ...

Performance counter for Outlook Finds?
What server performance counters (if any) are there for monitoring the number of "Find" requests that Outlook clients make? -GT ...

finding calendar and data info
I had to move my primary hard drive into another computer. It is now running as a slavein the new computer. I'm unable however, to find my calendar and contacts from Outlook 2003 when I open the program on the new computer. If I attempt to open the program from the F drive (the drive assigned in the new computer to the hard drive that was moved from another computer) it tells me the program is not installed. If I open Outlook 2003 from the C drive of the new computer, of course, it doesn't find the old calendar and contacts. I would appreciate any and all assistance in findin...

Conditional Formatting VBA with formula to find string
Hi, Please can I get some help.... I need to create a conditional format VBA in Excel 2007 (because I have many conditions to include) and I don't know how to do it... The Action Required: If Product A appears in any text string in range($C$95:$C$300) then colour that cell RED, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell BLUE, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell GREEN, and so on through 41 products... Data - I have a list of about 41 Product names in range Z...

Using sdk to find out if an Account already exists
Hi, Want to use sdk to see if an Account already exists , all i have is its name. I've looked at Retrieve method but it requires the Guid. Any equivalent call to Retrieve that just takes name? Or a way to get the Guid from the name? Sample code would be great if available. Thanks for all help. John Hi John, The method you are looking for is RetrieveMultiple as only the guid search gives the certainty to retrieve only one record. All explanations are in the sdk but as the sdk sample is nearly the exact code you want, here it is : // Set up the CRM Service. CrmService service =3D new ...

Find number of weekdays and wekend days given a total number of da
Hello, I have a question for the gurus here. I am working on a spreadsheet where in column A I have Total # of Vacation days. Example: # Vac Days --- # Weekdays --- # Weekend days 75 55 20 44 32 12 25 19 6 I figured this out by simply creating a list like this 1 Weekday 2 Weekday 3 Weekday 4 Weekday 5 Weekday 6 Weekend 7 Weekend And so on... And then doing a countif to figure out how many of either on...

Doesn't find files w/spaces in name
There are a lot of folders and Excel spreadsheets on our shared drive that I need to open. Most of the folders and file names are named like: get reports\reports sep 2004.xls When I try to open one, Excel doesn't find it and I think it's because there are spaces in the folder names and/or file names. Is there a setting in Excel that will help me get around this? This was working until I had a glitch in Excel and the technician fixed the glitch and this problem appeared. Any help would be appreciated. I found an answer and so far it works. "C:/program files/microsoft off...

SQL statement to find a particular column within all tables
I am looking for a query that will allow me to find all instances of a particular column within all tables so I know where they are all located. Does anyone have such a query they would be willing to share? Thank you. Pam, I posted this query a few months aback on my blog (http://dynamicsgpblogster.blogspot.com/2008/03/in-past-days-i-have-found-lot-of-people.html), but here is the excerpt: select distinct rtrim(objs.name) from syscolumns cols inner join sysobjects objs on (cols.id = objs.id) inner join sysindexes indx on (cols.id = indx.id) where (cols.name = 'ACTINDX') and (ob...

Multiple Instances of Manager Program
Greetings I have a situation where it might become necessary to have multiple people running the SO Manager program at the same time. Doing edits and changes primarily. Has anyone run up against any performance/stability issues under such circumstances. Does this cause any instability problems with the POS frontend also operating at the same time? The reason I mention this is that we did some experimentation with having an MS Access forms app try and edit some data in the Customer table and it would cause the frontend to crash if we were operating on the same record. We we...

Problem displaying string data in Edit box
Hi, I am trying to display a CString in an Editbox. In the dialog, I have set the edit box to multiline, read-only, auto vertical scroll and want return. I set the Edit box with the CString variable when a serial handler occurs. The serial handler code is obtained from http://www.codeproject.com/KB/system/serial.aspx and have integrated into my main dialog. I have created a edit box variable in my dialog and create a member variable within the main dialog window. When the serial handler is called, I store the serial message into the CString variable and use SetwindowText() to display the st...

formula to add a number to a long string **
I'm not quite sure how to best describe this: I need a formula that will take the example below and return the "final result" Col. A = 24.43.234.555 and Col B= 23 making Final Result = 24.43.234.578 Is there a way to do this? This seems to work: =LEFT(A1,LEN(A1)-3)&(RIGHT(TEXT(A1,"0"),3))+23 HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "MMangen" wrote: > I'm not quite sure how to best describe this: > > I need a formula that will take the e...