Extract non-alphanumeric character and all alphanumeric to right of non-alphanumeric

[alb58]

Hi,

I have been fighting with this for a few days. I am trying to find the first non-alphanumeric character in a cell then remove that character and all characters following. The data I am working with is in a csv file and looks as follows:

Bus Stop
Assembly / Ex...
In Class
Yard, Corrido...

I want to remove everything after and including "/" as well as everything after and including "," so the data needs to end up looking like:

Bust Stop
Assembly
In Class
Yard

Once I have cleaned up the data it is going to be copied to another worksheet and, as the original csv file will not be kept, there can't be a link back to the original worksheet.

I have used =LEFT(D24,FIND("/",D24)-1) which works on the cells with "/" in them but returns #VALUE (of course) for the cells with "," in them. In an attempt to allow for the non-alphanumeric character being either / or , I have tried using IF with LEFT, I have tried OR with IF and LEFT, I have tried using an array {"/",","} but have not been able to make anything work. I would really appreciate assistance with this problem.

Thank you.

 

[Aladin Akyurek]

B2:

=TRIM(LEFT(A2,LOOKUP(9.99999999999999E+307,SEARCH({"/",","},A2&"/")-1)))

where A2 houses a string to process.

 

[alb58]

B2:

=TRIM(LEFT(A2,LOOKUP(9.99999999999999E+307,SEARCH({"/",","},A2&"/")-1)))

where A2 houses a string to process.

Thank you very much Aladin. I appreciate your assistance very much.

 

[Aladin Akyurek]

Thank you very much Aladin. I appreciate your assistance very much.

Glad to help. Thanks for providing feedback.

 

[strooman]

In addition. I see a lot of questions about replacing characters. I think the user defined and very versatile function "dhTranslate" from Ken Getz from the book "VBA Developer's Handbook" comes in very handy and never let me down. I think it's pretty much self explaining. Adjust to suit your needs.

Dutch version (delimiter is semicolon): Formula [B2] =dhTranslate(A2;"./,…";"")
English version (delimiter is comma): Formula [B2] =dhTranslate(A2,"./,…","")

Public Function dhTranslate(ByVal strIn As String, _
ByVal strMapIn As String, _
ByVal strMapOut As String, _
Optional lngCompare As VbCompareMethod = vbBinaryCompare) As String
' In:
'   strIn:
'       String in which to perform replacements
'   strMapIn:
'       Map of characters to find
'   strMapOut:
'       Map of characters to replace.  If the length
'       of this string is shorter than that of strMapIn,
'       use the final character in the string for all
'       subsequent matches.
' Example:
'   dhTranslate("This is a test", "aeiou", "AEIOU") returns
'     "ThIs Is A tEst"


    Dim lngI As Long
    Dim lngPos As Long
    Dim strChar As String * 1
    Dim strOut As String


    ' If there's no list of characters
    ' to replace, there's no point going on
    ' with the work in this function.
    If Len(strMapIn) > 0 Then
        ' Right-fill the strMapOut set.
        If Len(strMapOut) > 0 Then
            strMapOut = Left$(strMapOut & String(Len(strMapIn), _
            Right$(strMapOut, 1)), Len(strMapIn))
        End If


        For lngI = 1 To Len(strIn)
            strChar = Mid$(strIn, lngI, 1)
            lngPos = InStr(1, strMapIn, strChar, lngCompare)
            If lngPos > 0 Then
                ' If strMapOut is empty, this doesn't fail,
                ' because Mid handles empty strings gracefully.
                strOut = strOut & Mid$(strMapOut, lngPos, 1)
            Else
                strOut = strOut & strChar
            End If
        Next lngI
    End If
    dhTranslate = strOut
End Function

© Ken Getz & Mike Gilbert 1998

 

[István Hirsch]

Give this formula a try too, enter it in B2:

=REPLACE(A2,MIN(SEARCH({" /","/",","},A2&" /,")),500,"")

 

[Rick Rothstein]

In addition. I see a lot of questions about replacing characters. I think the user defined and very versatile function "dhTranslate" from Ken Getz from the book "VBA Developer's Handbook" comes in very handy and never let me down. I think it's pretty much self explaining. Adjust to suit your needs.

I think this somewhat shorter code will do the same thing as the code you posted does (for Excel only)...

Function rrTranslate(ByVal strIn As String, _
                     ByVal strMapIn As String, _
                     ByVal strMapOut As String, _
                     Optional lngCompare As VbCompareMethod = vbBinaryCompare) As String
  Dim X As Long
  For X = 1 To Len(strMapIn)
    strIn = Replace(strIn, Mid(strMapIn, X, 1), Mid(strMapOut, Application.Max(1, Application.Min(X, Len(strMapOut))), 1))
  Next
  rrTranslate = strIn
End Function