Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find and Replace (Excel 2000)

    hi, I have a list of names in an Excel spreadsheet which need to be uploaded into a system called pickwick. The names need to be surrounded by quotation marks. Is there a quick way to do this? I have tried find and replace with no real success.

    Thanks

  2. #2
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and Replace (Excel 2000)

    Assuming the test you need is arranged in a column(s)... Put the following formula in an adjacent cell

    =""""&A1&""""

    Assumes you are refering to cell A1, amend to suit and then copy down as far as you need. Now copy the results of those formulas to whereever you need them.

    Regards
    Peter

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and Replace (Excel 2000)

    fantastic!! Hours have been saved - but what exactly is the formula saying?

    Michelle

  4. #4
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and Replace (Excel 2000)

    OK.... The formula :

    =""""&A1&""""

    Can also be written as:

    =CONCATENATE("""",A1,"""")

    Look in help for an explanation of CONCATENATE. The ampersand (&) saves you having to type CONCATENATE and also is analgous with the the plus (+) sign in numeric formulas.

    The quotes.... I took a guess.. many programming environments need special treatment of quotation marks. Entering """", four sets of quotation marks, simply tells Excel that you want a single quotation mark. So, in English, the formula:

    =""""&A1&""""

    Can be expressed as:

    ="This first string" + "This second string" + "This third string" + "Etc, etc....."

    Hope this helps clarify rather than confuse!

    Regards
    Peter

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and Replace (Excel 2000)

    Another formula that returns the same result is

    =CHAR(34)&A1&CHAR(34)

    CHAR(34) returns the character with ASCII code 34, that is the double quote ".

  6. #6
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and Replace (Excel 2000)

    Hi Hans

    Mine has less typing.... (Humour ok!)... I said I was guessing too!

    Regards
    Peter

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and Replace (Excel 2000)

    Peter,

    Your formula is entirely correct and probably the shortest way to enclose a string in quotes. Your reasoning is also spot on: since a quote is used to delimit a string, you can't just insert a " in a string; the workaround is to insert two of them: "". So a string consisting of one double quote must be specified as """": the first " is the opening delimiter, the second and third " together insert one ", and the last " is the closing delimiter.

    But sometimes, this kind of notation becomes confusing; in such cases, I use CHAR(34), or in VBA Chr(34).

  8. #8
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and Replace (Excel 2000)

    Hi Hans

    Noted.. Peace.

    Regards
    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •