-
Sep 17th, 2021, 12:41 PM
#1
Thread Starter
New Member
String has quote by default. How to remove that quote from string?
Hi,
My string has charactes(alphabat + numeric). That string values will change in every row, and come from cell value in excel. And I want to find that value in another worksheet. It never find, while the same value available in another sheet. Through Watch window I found that string has quotes ( "RCTXB0180"), because it stored as String. While value in another sheet is without quote. Please guide How to remove that quote from string. Or any other way to find that value from range. I do not want to use "For and next " or Do loop, because I want that if string value not find on range, then a new line insert and put the value of string in that new line. I also tried Replace, but it would also not work
Last edited by 0704gaurav; Sep 17th, 2021 at 12:53 PM.
-
Sep 17th, 2021, 02:37 PM
#2
Re: String has quote by default. How to remove that quote from string?
Alpha numeric values are strings and strings always show with quotes around them when you look at the values. The quotes are not actually part of the value. I doubt this is your problem, more likely you are getting into a case of case sensitivity RCTXB != RcTXB
In any case this sounds like and Excel VBA question and should be moved to the office section.
-
Sep 17th, 2021, 03:24 PM
#3
Thread Starter
New Member
Re: String has quote by default. How to remove that quote from string?
Thank you very much sir for your reply...!!!
But that is not case of case sensitivity. I know that quote is not a part of value, but when it looking the value in another worksheet, than it search with quote as i saw in watch window. Hence the value in excel another worksheet is not with quote so vba not recognize it as same value as string.
-
Sep 17th, 2021, 04:37 PM
#4
Re: String has quote by default. How to remove that quote from string?
Excel has its own "hillbilly variant" format for cell contents.
It can do crazy things. If you try to stuff "123" into a cell via its automation interface it tends to coerce the value to some numeric type that it prefers (Double?).
The workaround is to assign "'123" (with an apostrophe) instead. That even works in the Excel sheet view manually. It will complain mildly (usually there is some visual indication that varies by Excel version) but it works.
In general Excel sucks. But it kind of has to. For its intended purpose it can be a great tool, but it should never be used as a datastore or publishing medium.
-
Sep 17th, 2021, 05:06 PM
#5
Re: String has quote by default. How to remove that quote from string?
Code:
s = Replace$(s, """", "") ' Is that what you're looking for?
That assumes you're pulling the cell values into a VB6 string before you're processing them.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Sep 17th, 2021, 05:12 PM
#6
Re: String has quote by default. How to remove that quote from string?
But if the value contains quotes it gets more complicated than that.
I think he's just lost trying to make sense of the Watch Window display.
-
Sep 17th, 2021, 05:59 PM
#7
Thread Starter
New Member
Re: String has quote by default. How to remove that quote from string?
Sir, Value don't have quotes. But when the value store in string then it has automatically have quotes.
Dim x as string
X= worksheet1. Range("e" & z)
Worksheet2. Range("e3:e"& v) .find(x)
Now sheet1 value is RCTX0180, but in watch window showing "RCTXB0180",which is understandable.but when string x value search between the range given, then it come nothing, while the same value available in given range. Because value stored in string with quotes and in worksheet cell contain the same value but without quote
-
Sep 17th, 2021, 06:01 PM
#8
Thread Starter
New Member
Re: String has quote by default. How to remove that quote from string?
I already tried that replace syntax many times but nothing change. It return with same value.
-
Sep 18th, 2021, 03:27 AM
#9
Re: String has quote by default. How to remove that quote from stringin VBA EXCEL?
have you tried the parameters for find? lookAt should allow to specify xlPart that should find the value between the quotes
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|