Results 1 to 9 of 9

Thread: String has quote by default. How to remove that quote from string?

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2021
    Posts
    4

    Unhappy 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.

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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.

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2021
    Posts
    4

    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.

  4. #4
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    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.

  5. #5
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    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.

  6. #6
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    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.

  7. #7

    Thread Starter
    New Member
    Join Date
    Sep 2021
    Posts
    4

    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

  8. #8

    Thread Starter
    New Member
    Join Date
    Sep 2021
    Posts
    4

    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.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
  •  



Click Here to Expand Forum to Full Width