Find And Delete Quotation Marks At The End Of A String

  • Hi, I'm using a query that dumps information into Excel. The query automatically creates a formula in each each cell. For instance if the first value in the query showed FUNSTUFF, after dumping it into Excel, the contents of the cell would show ="FUNSTUFF"
    I can just use a find and replace to get rid of the leading =", but sometimes the values of the cells are supposed to have a quotation mark, so using the same method to remove the trailing quote won't work. I need a way to search each cell, check if the last character in the string is a quote, and if it is, delete it. Thanks in advance for any help!

  • Re: Find And Delete Quotation Marks At The End Of A String


    I am not sure if I understand what you need, I assume you want to remove quotation: Here is a formula that will work to remove the quotation marks, and if there aren't any, will use the cell value as is. Hope this is what you needed.


    =IF(ISERROR(FIND("""",A2,1))=TRUE,A2,LEFT(A2,FIND("""",A2,1)-1)&MID(A2,FIND("""",A2,1)+1,(FIND("""",A2,FIND("""",A2,1)+1))-(FIND("""",A2,1))-1)&RIGHT(A2,LEN(A2)-FIND("""",A2,FIND("""",A2,1)+1)))

  • Re: Find And Delete Quotation Marks At The End Of A String


    Acutally, I was looking for vba code to do this. On average, the query will put in one to three hundred rows and roughly 50 columns. The program automatically makes a formula out of any string value (it ignores numbers and blanks) and puts an equal sign and quotation mark at the beginning of the string and another quotation mark at the end of the string in each cell. Since I have no control over the output of the program that exports the data, I need to figure out a way to remove what it adds into each cell.

  • Re: Find And Delete Quotation Marks At The End Of A String


    Like this?

    Code
    If Left(cell, 2) = "=""" Then cell = Mid(cell, 3)
        If Right(cell, 1) = """" Then cell = Left(cell, Len(cell) - 1)

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Find And Delete Quotation Marks At The End Of A String


    Hi bored,


    This works for me. It looks for =" at the beginning of a cell and strips it to it's value.


    [vba]
    Sub DeleteExtras()


    Dim cel As Range
    Dim rng As Range
    Dim stripval As String


    'get range to work on
    Set rng = ActiveSheet.UsedRange


    'do each cell in range
    For Each cel In rng
    'if starts with ="
    If Left(cel.Formula, 2) = "=""" Then
    'strip the 'formula'
    stripval = cel
    'write the cleaned value back to the cell
    cel.Value = stripval
    End If
    Next cel


    End Sub


    [/vba]


    Cheers,


    dr

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!