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!
Find And Delete Quotation Marks At The End Of A String
-
-
-
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
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 celEnd Sub
[/vba]
Cheers,
dr
-
-
Re: Find And Delete Quotation Marks At The End Of A String
Perfect! I got what I was looking for. Thanks for the help guys.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!