Trying to avoid quote and comma stripping in formulas

slick225 · Apr 17th 2006

I have to create a formated file to test our system every other week, I want to create a File Generator using Excel and the replacement values forumla "& A1 &". However the file is in a CSV format containing a lot of quotes and when I attempt to use a line of the file in a formula with "& <Cell> &" excel reports errors, if you accept excels help it strips out the quotes and commas. Anyway around this? having to have a user save the file is CSV format would be to complicated.

Sample file attached.

**Batman** · Apr 18th 2006

Re: Trying to avoid quote and comma stripping in formulas

Hi slick225,

It can be a bit confusing, but to get your head round how to display a set of quotes in a cell you need to be aware of the fact that to display quotes you need to firstly combine 2 sets of " within the " that denote the start and end of the string.

Hence to display solely " within a cell you need to enter
=""""

Once you do this you can include commas within the text as well. As far as I can see the following formula will give you the format you're after:

="""1"",""TEMPLATE"",""HEADER"",""SOURCESYS"","""&D2&""","""&D3&""",""SOURCECOMP"",""Source Company Name"","""&B4&""""

Hope this helps.

Regards,
Batman.

slick225 · Apr 18th 2006

Re: Trying to avoid quote and comma stripping in formulas

Thanks for the tip Batman, my file gen is coming along nicely thanks to your help.

Trying to avoid quote and comma stripping in formulas

Participate now!

Share