Posts by rockstar

    Re: Add Quotes to Comma Delimited String

    Thanks Anthony but I think I figured out how to fix the problem. I'm using duplicate column that I mentioned in one of above replies, to add brackets with quotes at the beginning and end of the string and asking to substitute comma separated values with (','). I think it is going to work otherwise I'll provide you with requested information. Thanks for looking into it.

    Re: Add Quotes to Comma Delimited String

    Hi Anthony,

    Just wondering if you could get it to work. I've posted original Substitute formula in previous reply. Can you please look into it or if you are busy just let me know how to find index of particular string value so that I can modify my formula to not quote brackets in a substring?

    Re: Add Quotes to Comma Delimited String

    Hi Smuzoen,

    Here is the formula I'm using. Thanks for looking into it.

    SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&"(" & SUBSTITUTE(B1," ","")&")","(","('"),",","' , '"),")","')")

    Ex: B1 = ABC (DEF),
    A1 = XYZ

    Current Output>C1= XYZ ('ABC ('DEF')')
    Desire Output>C1= XYZ ('ABC (DEF)')

    Re: Add Quotes to Comma Delimited String

    Hi Jindon/Smuzoen,

    The SUBSTITUE formula is working, however, it is adding quotes when there is a substring enclosed in brackets within the main string. Here is the example.

    Ex: A1 = ABC (DEF),
    A2 = XYZ

    Current Output> XYZ ('ABC ('DEF')')
    Desire Output> XYZ ('ABC (DEF)')

    Can you please tell me how to remove those quotes from the substring and add space between the two strings? Again I'm using SUBSTITUTE function suggested in above reply.

    Thanks for your help in advance.

    Re: Add Quotes to Comma Delimited String

    Thanks again to both of you.

    I was asked not to use macros so I resolved issue by duplicating the column that holds string with trailing commas and removed it using RIGHT, LEFT and LEN functions. Finally, in the SUBSTITUTE statement I included the duplicate column.


    Re: Add Quotes to Comma Delimited String

    Hi smuzoen,

    The above mentioned formula is working, however, when no value after comma is entered, it is giving empty brackets.

    The suggested UDF is also having similar issue when no string is entered after comma. REPLACE function works in UDF but if a string is entered after comma with couple of spaces, in the output those white spaces are also included (Output: XYZ('ABC',' DEF')).

    Ex: A1 = ABC,
    A2 = XYZ

    Output> XYZ ('ABC', '')

    Can you tell me how to remove those empty quotes if by accident comma is entered after first value in cell A1?

    Thanks for your time in advance.

    Hi All,

    I've a cell with values separated by commas. My task is to combine two cells one having comma separated values and another single text. The catch here is I've to add single quotes to each comma separated value.


    A1 = ABC,DEF,GHI
    A2= XYZ
    A3= XYZ('ABC', 'DEF', 'GHI')

    I tried using combination of SUBSTITUTE, TRIM AND CONCATENATION functions but the problem is I've to leave a space between comma separated values (like this A1 = ABC, DEF, GHI). Sometimes there may be data without spaces (as in A1 example).

    Can you please tell me how to get the value as in A3? Appreciate your time and help in advance.


    Re: Moving MS Excel data to a website via a web service

    Hi Kyle,

    Thanks for your response. As I'm new with WBS, this question may sound rhetorical. By 'what webservice expects' you mean what the schema is like? Can you please let me know what exactly you would need to know to help further.




    First of all please apologize if this is not appropriate place to start this thread. I'm exploring different options to get data from excel file to website using webservice. Can someone please suggest a way to get it done, I mean what type of add-ons are required, if any, and what would be the process like to move data?

    Thanks for your help in advance.


    Re: Automate Batch File

    I could see step 5 through Excel opened VBA editor and to the left corner is workbook_open event. But not sure what to enter in code space as I'm naive to VB. Hope this info helps you in assessing my coding skills and guide me through. Can I use "at" command in DOS mode to schedule this batch file?

    Re: Automate Batch File

    Hi Iwrk4dedpr...Thanks for your response. Could you be more specific about the steps to automate my batch file that will open a Excel file. On my machine, under start>Programs>Microsoft Office>"I have" Digital Certificates for VBA Projects and when I click it asked me to enter certificate name. I entered my ID and clicked ok but nothing appeared after that. Appreciate if you can tell me another way to get that done. I can access VB Editor through Excel 2007. Will I be able to work there to create the required function on batch file? Thanks.

    Hi...I have batch file to open my excel file and I wuold like to automate the batch file. Basically, I need open my excel without manual intervention and save it as XML data file. I believe, we cannot automate opening excel file with a macro as it has to run within the file, therefore I'm looking into this option after searching different forums. If anybody have experience automating batch file, please let me know. Also if you can share macro code to save as on existing XML data file, would be very helpful. Thanks.