Display the text before the 6th instance of a period in a string (MS Project or VBA)

[sahderach]

Hello!

I'm trying to figure out a macro that can display the text before the 6th instance of a period in a string.

=TEXTBEFORE(".",6) works perfectly in Excel but I need this to work in MS Project and I don't know of a formula that will do this using MS Project's list of commands.

If this is possible using MS Project's limited functions, that would be ideal but I think a macro would be easier.

I'm not very strong in VBA so any help would be greatly appreciated, thanks!

Original	TEXTBEFORE 6th "."
1.2.3.4.5.6.7.8	1.2.3.4.5.6
1.2.3n.4.5.5.6.7	1.2.3n.4.5.5
1n.2n.3n.4n.5.6.7.8	1n.2n.3n.4n.5.6
1.2.3.3n.5.9n.10.8n.9n.10n	1.2.3.3n.5.9n

 

[johnnyL]

I am late to the party but consider the following:

Formula approach:

Book1
	A	B	C	D
1	Original String	Before 6th	After 6th
2	1.2.3.4.5.6.7.8	1.2.3.4.5.6	7.8
3
4		1.2.3.4.5.6	7.8
5
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),6))-1)
C2	C2	=REPLACE(A2,1,FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),6)),"")

VBA approach:

Sub GetNthCharactersFromACell()
'
'  Display the text before the 6th instance of a period in a string
    Range("B4").Value = Evaluate("=LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2,""."",CHAR(1),6))-1)")
'
'  Display the text After the 6th instance of a period in a string
    Range("C4").Value = Evaluate("=REPLACE(A2,1,FIND(CHAR(1),SUBSTITUTE(A2,""."",CHAR(1),6)),"""")")
End Sub

 

[johnnyL]

Perhaps better, more adjustable VBA code:

Sub GetNthCharactersFromACellV2()
'
    Dim NthCharacter            As Long
    Dim CellToCheck             As String
    Dim ReplacementCharacter    As String
'
    NthCharacter = 6                                                                                                        ' <--- Set this to the Nth character that you seek
    CellToCheck = "A2"                                                                                                      ' <--- Set this to the Cell to check
    ReplacementCharacter = "CHAR(1)"                                                                                        ' <--- Set this to the character to mark the string with
'
'  Display the text before the 6th instance of a period in a string
    Range("B4").Value = Evaluate("LEFT(" & CellToCheck & ",FIND(" & ReplacementCharacter & ",SUBSTITUTE(" & CellToCheck & _
            ","".""," & ReplacementCharacter & "," & NthCharacter & "))-1)")                                                ' Evaluate the value of the formula
'
'  Display the text After the 6th instance of a period in a string
    Range("C4").Value = Evaluate("REPLACE(" & CellToCheck & ",1,FIND(" & ReplacementCharacter & ",SUBSTITUTE(" & _
            CellToCheck & ","".""," & ReplacementCharacter & "," & NthCharacter & ")),"""")")                               ' Evaluate the value of the formula
End Sub