Excel Tips - Use Inverted Commas In Excel Formula

I am trying to generate .sql scripts to drive a DTS package, I am using Excel as the hub as certain elements within the script change on a weekly basis. So I am the stage where I want to export my worksheet into .txt/.sql format. However, when I use the FileFormat:=xlText export function, the exported text file includes inverted commas around code which has commas.

Is there a way I can export an excel worksheet into a .txt/.sql compatible format which includes commas which doesn't include inverted commas?

Hi all,

I'm converting an .xlsx excel sheet, into a tab delimited text file, to import data into a shopping cart.

When I do this, excel adds inverted commas (") around each cell of data that has commas in it, plus it doubles the inverted commas around existing inverted commas (resulting in "")!

(It didn't seem to do this with the old .xls files)

Any info on how I can prevent this happening?

Thanks!

Hi,
If I write a multi-line text in a cell (then go down with alt+enter) and after copy the cell pasting on Notepad, it display before inverted commas and after textual content.
Instead, if I select directly the content from the formula bar, it isn't happen.
Do exist a way for copy and paste directly from the cell without select from the formula bar?
Maybe with a macro?

Thank
Marco

__________________
nuove idee regalo compleanno, servizio realizzazione siti internet, forum scuole superiori, assistenza pantografo

When I try to copy text with quotes(inverted commas) on excel sheet, it removes those inverted commas. For example when I try to copy "Mr Excel" on excel sheet it removes quotes( inverted commas) and just place Mr Excel on a cell.
Is there any way so excel should not remove those quotes.

Regards,
Mushi

Hi,
If I write a multi-line text in a cell (then go down with alt+enter) and after copy the cell pasting on Notepad, it display before inverted commas and after textual content.
Instead, if I select directly the content from the formula bar, it isn't happen.
Do exist a way for copy and paste directly from the cell without select from the formula bar?
Maybe with a macro?

Thank
Marco

__________________
nuove idee regalo compleanno, servizio realizzazione siti internet, forum scuole superiori, assistenza pantografo

Hello,
I had two requests
Request 1
I want to insert the following If condition using VBA code, but the inverted commas are creating a mess. =IF(C2="","Created",IF(C2="*** Created ***","Created","Other Changes"))

Please note that the * comes from raw data and that is also adding to the mess.

Request 2
after adding the above formula wanted to randomly pick 30% sample(Created 30% and Other changes 30%) using macro .

Can someone please help me out, below is layout how it would look on excel.


Data Classification Random Sample =IF(C2="","Created",IF(C2="*** Created ***","Created","Other Changes")) =Rand ***Created *** =IF(C2="","Created",IF(C2="*** Created ***","Created","Other Changes")) asdffa =IF(C2="","Created",IF(C2="*** Created ***","Created","Other Changes"))



Regards

How would I for each cell add inverted commas to the begging and end of what ever is in each cell, apart from manually typing it all in?

Hi i currently have output on my a userform.

What i want to do is have inverted commas around the variable CodeStr as shown below. When i put say & "CodeStr" & "". It no longer see this a variable. How could i do this?



Code:

AddressStr = AddressStr & Found.Offset(0, -2).Value & " " & vbTab & " Document Title is " & "" & CodeStr & "" & vbCrLf

I am trying to use something like to following to take a variable string from an input box to modify the formatting of particular cells.

.NumberFormat = "General" & strUnit & ";[Red]General" & strUnit

The result I want is something like 4m2 or 5km depending on the text entered which becomes the string variable strUnit.

If I enter "m2" with inverted commas in the input box, it works. But what I want is to enter m2 or km or whatever without the inverted commas and have the code add them.

Does anyone know how this may be done?

Hi. Quick question I hope...

I am looking for the syntax to concatenate a string variable and a fixed text string to return a named range in a call to a range. (See "Problem" below.)


BACKGROUND / SCENARIO:

I have 2 Named Ranges.

Name 1 = "namFormCurUserDefSetting_ComboBox1"
Name 2 = "namFormDefUserDefSetting_ComboBox1"

Both have the same Suffix:
Name 1 Suffix = "_ComboBox1"
Name 2 Suffix = "_ComboBox1"

But the Prefix differs:
Name 1 Prefix = "namFormCurUserDefSetting"
Name 2 Prefix = "namFormDefUserDefSetting"

A Select Case Statement assigns either Prefix String 1 or Prefix String 2 to a String Variable called strSettingsChoice


PROBLEM:
What is the syntax to Concatentate the Prefix (a String Variable) and the Suffix (a string of text not assigned to a variable) to produce the complete Named Range Name to use in a Range Expression?

The main problem I am having is how to manage the Inverted Commas which must enclose the result of the concatenation (ie the complete Named Range Name), without making the Variable Name look like it is actually to be read as another text string.

Should I use single, double or treble inverted commas?
I'd prefer not to Chr(xxx) if possible.

Do I use them at the start and end of the concatention, or where?


DESIRED RESULT:
This is the fixed text code that works without error, which I would like to replace with the concatenation phrase.

Code:

 
  
    Case 1 
 
    Me.ComboBox1.Value = Range( " namFormCurUserDefSetting  _ComboBox1   " ).Value

  
    Case 2 
 
    Me.ComboBox1.Value = Range( " namFormDefUserDefSetting  _ComboBox1   " ).Value

 

UNSUCESSFUL ATTEMPT (1):

Code:

 
  
    Case 1 
 
    Me.ComboBox1.Value = Range( """  & strSettingsChoice &  "   _ComboBox1   " ).Value
  
  
    Case 2 
  
    Me.ComboBox1.Value = Range(   """  & strSettingsChoice &  "   _ComboBox1   " ).Value

 

UNSUCESSFUL ATTEMPT (2):

Code:

 
  
    Case 1 
 
    Me.ComboBox1.Value = Range( ""  & strSettingsChoice &  "   _ComboBox1   "  &  "" ).Value
  
  
    Case 2 
  
    Me.ComboBox1.Value = Range(   ""  &  strSettingsChoice &  "   _ComboBox1   "    &  ""   ).Value

 

All atempts so far have errored out with:

Run-time error '1004':
Method 'Range' of object '_Global' failed

Thanks in advance.