Delete all text before character (1 Viewer)

[mtairhead]

I've search the forums...If you can find a thread that discusses this, or can give me some advice on this topic, let me know.

Is there any built-in function that will allow me to delete any text before a certain point in a record... Or perhaps a way to use the REPLACE function to do so?

All of my records look like this (They're ticker symbols):
AMZN:Amazon, Inc
WMT:Wal-Mart Inc.
A:Agilen Tech Inc
MSFT:Microsoft Inc.

I just want the company name...Unfortunately, the ticker symbols aren't always the same length, so I can't use the left or right function.

I need to delete the colon and anything to the left of the colon. I've thought of a great use of the replace function for this purpose...I used wildcards...Too bad it didn't work!

Just The Company Name: Replace([Ticker Symbol and Name],"*" & ":","")

Any ideas?

 

[WayneRyan]

m,

Mid(Ticker, InStr(1, Ticker, ":") + 1)

Wayne

 

[ChrisO]

A tad more complex but a good coding exercise anyhow: -
(A2K and up)

Option Explicit
Option Compare Text


Sub Test()
    Dim strText        As String
    Dim strStart       As String
    Dim strMask        As String
    Dim strReplacement As String
    
    strStart = ":"
    strMask = ".,"
    strReplacement = ""
    
    strText = "AMZN: Amazon , Inc"
    MsgBox ReplaceByMask(strText, strStart, strMask, strReplacement)
    
    strText = "WMT:Wal-Mart Inc."
    MsgBox ReplaceByMask(strText, strStart, strMask, strReplacement)
    
    strText = "A:Agilen Tech Inc"
    MsgBox ReplaceByMask(strText, strStart, strMask, strReplacement)
    
    strText = "MSFT:Microsoft Inc."
    MsgBox ReplaceByMask(strText, strStart, strMask, strReplacement)

End Sub


Public Function ReplaceByMask(ByVal strSearchOn As String, _
                              ByVal strStartAt As String, _
                              ByVal strMask As String, _
                              ByVal strReplacement As String) As String
    Dim lngCharPos As Long
    
    strSearchOn = Mid$(strSearchOn, InStr(strSearchOn, strStartAt) + 1)
    
    For lngCharPos = 1 To Len(strSearchOn)
        If Mid$(strSearchOn, lngCharPos, 1) Like "[" & strMask & "]" Then
            strSearchOn = Replace(strSearchOn, Mid$(strSearchOn, lngCharPos, 1), strReplacement)
        End If
    Next lngCharPos
    
    ReplaceByMask = strSearchOn

End Function

Hope that helps.

Regards,
Chris.

 

[strep21]

I'm a big fan of the SPLIT function, so I would use :

Function SplitAgain(strIN As String, strDelimiter As String)
'strIN like "AMZN : Amazon, Inc"
'strDelimiter = ": "
'Use like : SplitAgain("AMZN : Amazon, Inc",": ")
'Output = Amazon, Inc
Dim var1 As Variant
var1 = Split(strIN, strDelimiter)
SplitAgain = var1(1)

End Function

 

[mtairhead]

!!!

Thank you. I had no idea it would be so easy. I guess I could learn what each individual built-in text function does, in order to know I to combine them like that.

Thanks!