Select Text Directly after One of Two Special Character

[C_Texas_99]

I have two functions that return a portion of a string. Basically it looks for either an @ or a # and returns the text directly after it. The @ sign is easy because the last character after the text will be a space, no matter what. However, how do I work with the # because the character delimiter can be either a space or a period to end the sentence. I cannot for the life of me figure this one out. It doesn't matter which character it is, I just need to be able to get the text before. So I need to check if it is either or and then it returns the same value no matter what.

Any ideas? Thank you in advance!!

This is what I have so far:

Function ProductName(ContactReason) As String

    Dim ProductPosition As Variant
    Dim TextAfterPound As String
  
    'Find Position of #
    If IsNull(ContactReason) Then
        ProductPosition = 0
    Else
        ProductPosition = InStr(1, ContactReason, "#")
    End If
        
    'Return portion of String after #
    If ProductPosition > 0 Then
        TextAfterPound = Mid(ContactReason, ProductPosition + 1, 100)
    Else
    TextAfterPound = ""
    End If
    
    'Return Product Name
    If ProductPosition = 0 Then
       ProductName = "Not Specified"
    Else
        ProductName = Left(TextAfterPound, InStr(TextAfterPound, ".") - 1)
    End If
End Function

[ajetrumpet]

so what do you want? the char after the pound sign or the REST of the string after the pound sign?

[C_Texas_99]

The text would look something like "#access Microsoft." or "#access." I want to select the first "word" after the pound sign. I am confused as to how to extract this data because the word can end with a period or a space.

P.S. - Right now my code pulls everything between the # and the period at the end of the sentence. I just need the first words after the #.

[ajetrumpet]

The text would look something like "#access Microsoft." or "#access." I want to select the first "word" after the pound sign. I am confused as to how to extract this data because the word can end with a period or a space.

P.S. - Right now my code pulls everything between the # and the period at the end of the sentence. I just need the first words after the #.

if the string ends with a period or space ONLY, you can use less code like this:

dim s as string
dim sPos as long
dim sResult as string

sResult = ""
sPos = instr("YOUR STRING", "#") + 1
s = mid("YOUR STRING", sPos, 1)

do until s = "." or s = " "

   sResult = sResult & s
   sPos = sPos + 1

      s = mid("YOUR STRING", sPos, 1)

loop

YOUR OUTPUT OBJECT = sResult 

more variables of the sentence end character would call for ASC() & CHR() functions to cover the basis

[C_Texas_99]

Okay, it is working; however, it is not printing the results I expected. Well actually is sort of is.

For example:

ims.ims
portal.portal
ims. Told him to me sure he is....

It is printing things twice or continuing to the next space or period. I just need it to look for that first space or period. How do I alter this loop to stop when it sees the first space or period?

Thank you so much for your help!! Your code is much cleaner than mine!

[ajetrumpet]

my code does do that as far as i know

post your and i'll look at it

[C_Texas_99]

Okay, fantastic...

Option Compare Database

Function ProductName(ContactReason) As String

    Dim ProductPosition As Long
    Dim TextAfterPound As String
    Dim ProductResult As String
    
    ProductResult = ""
    
    ProductPosition = InStr(1, ContactReason, "#")
    
    TextAfterPound = Mid(ContactReason, ProductPosition + 1)
    
    Do Until TextAfterPound = "." Or TextAfterPound = " "
    
        ProductResult = ProductResult & TextAfterPound
        ProductPosition = ProductPosition + 1
        
            TextAfterPound = Mid(ContactReason, ProductPosition, 1)
    
    Loop
    
    ProductName = ProductResult
    
End Function

The 'TextAfterPound' could be a string such as "#text." or "#text continue string." If that makes sense. Or even "Holly #text. Another string."

[ajetrumpet]

there are a few mistakes. here is the right one

Function ProductName(ContactReason ) As String

    Dim ProductPosition As Long
    Dim TextAfterPound As String
    Dim ProductResult As String
    
    ProductResult = ""
    
    ProductPosition = InStr(1, ContactReason, "#") + 1
    
    TextAfterPound = Mid(ContactReason, ProductPosition, 1)
    
    Do Until TextAfterPound = "." Or TextAfterPound = " "
    
        ProductResult = ProductResult & TextAfterPound
        ProductPosition = ProductPosition + 1
        
            TextAfterPound = Mid(ContactReason, ProductPosition, 1)
    
    Loop
    
    ProductName = ProductResult
    
End Function 

[C_Texas_99]

Oh that is fantastic, it works like a champ!! That is so great, now I don't have to worry about how the user inputs the data. I don't have to 'make' use a certain format.

This is great, you were a big help, ajetrumpet! I appreciate it more than you know. This was driving me crazy!

[C_Texas_99]

Actually, I thought of one other thing. I had this in my original code. What if the user does not enter the special character. Can I just set the field as "Not Specified"? I originally determined this using the ProductPosition. If it was 0 then I set the ProductName to "Not Specified".

[ajetrumpet]

sorry man, i have no idea what you're talking about here

[C_Texas_99]

Alright, thank you so much for all of your help!! Much appreciated!