How to always return a string to a cell from vba (excel)

[Mightyginger]

Hi,

I am using a function to parse and return parts of a string. The string would look like this
"FEDF-JUN10-99.8125-CALL". I am using "-" as the delimiter and the function is below. All of that is fine.

However.

I then use another Sub routine to go through and output amd at one point it returns "JUN10" when it parses that section and Excel annoyingly keeps formatting that as "10-Jun" and putting it in as a date, so 10-Jun-1010. How can I stop that and get only text please?

Function returnTextFromDelimitedString(inputString As String, inputDelimiter As String, startingNumberofDelimiter As Integer) As String
Dim i As Integer
Dim saveString As Boolean
Dim delimitersFound As Integer
Dim tempString As String
delimitersFound = 0
i = 1
Do Until i = Len(inputString) + 1
If (Mid(inputString, i, 1) = inputDelimiter) Then
i = i + 1
delimitersFound = delimitersFound + 1
End If

If (i <= Len(inputString)) And (delimitersFound = startingNumberofDelimiter) Then
tempString = tempString & Mid(inputString, i, 1)
End If
i = i + 1
Loop

returnTextFromDelimitedString = tempString

End Function

 

[kjv1611]

Function returnTextFromDelimitedString(inputString As String, inputDelimiter As String, startingNumberofDelimiter As Integer) As String
Dim i As Integer
Dim saveString As Boolean
Dim delimitersFound As Integer
Dim tempString As String
delimitersFound = 0
i = 1
Do Until i = Len(inputString) + 1
    If (Mid(inputString, i, 1) = inputDelimiter) Then
        i = i + 1
        delimitersFound = delimitersFound + 1
    End If
    
    If (i <= Len(inputString)) And (delimitersFound = startingNumberofDelimiter) Then
        tempString = tempString & Mid(inputString, i, 1)
    End If
    i = i + 1
Loop

returnTextFromDelimitedString = [highlight]"'" & [/highlight]tempString

End Function

The apostrophe, or single quote, tells Excel that the value is text.

 

[SkipVought]

FYI: faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mightyginger]

Great. Thanks!!

 

[mscallisto]

does the split function help?

Option Explicit
Dim txt As String
Dim x As Variant
Sub z()
 txt = "FEDF-JUN10-99.8125-CALL"
 x = Split(txt, "-")
 MsgBox (x(0) & vbCrLf & x(1) & vbCrLf & x(2) & vbCrLf & x(3))
End Sub

yields:
FEDF
JUN10
99.8125
CALL