Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

remove double quote marks when pasting into notepad

I have set up an excel file that helps put together different text components using the vlookup and concatenate formulas for use in one plain text document .

When I try to paste the finished result into notepad/textpad/word it adds extra double quote marks throughout the text. Does anyone know how to paste it without these marks?

Thanks!


Post your answer or comment

comments powered by Disqus
Im copying a cell in excel and pasting the content in notepad. The content
pasted in notepad automatically having a newline at the end. So the cursor is
moving to the next line automatically. How to get rid of this problem? I want
the cursor should be there at the end of that particular line. It looks like
a small problem. But I copy and paste from excel to notepad at least 1000
times a day. All the time Im pressing backspace to delete that extra line.
Can anyone help me here to get rid of this problem?
How to remove an extra line added by excel when pasting into notepad from a
cell in excel?

Hi,

I am trying to insert a double quote mark into two places inside a formula in a cell in Excel 2000. I use the csv file output to create a PHP webpage. PHP side works, but having problem with either too many double qoute marks, or not enough, appearing in the csv file. I use the pipe symbol as a separator instead of a comma.

I have attached a sample workbook with excess details stripped out.

Brief summary of what the difference in the two examples are

1/ getting a double quote mark at beginning and end of field where there should be no quote mark

2/ getting two double quote marks in the middle of the field data where only one double quote mark should be

Example 1
current csv files output of cells G2 & H2:

|"http://www.seocom.com/postcard/ephemera/tmb_vtc64.jpg""><BR><img src=""http://www.seocom.com/postcard/ephemera/tmb_vtc64r.jpg"

|"http://www.seocom.com/postcard/ephemera/vtc64r.jpg""><B>View Rear Image</B></A><BR><A HREF=""http://www.seocom.com/postcard/ephemera/vtc64.jpg"|

Example 2
What I need for G2 & H2 to output in the csv file:

|http://www.seocom.com/postcard/ephemera/tmb_vtc64.jpg"><BR><img src="http://www.seocom.com/postcard/ephemera/tmb_vtc64r.jpg

|http://www.seocom.com/postcard/ephemera/vtc64r.jpg"><B>View Rear Image</B></A><BR><A HREF="http://www.seocom.com/postcard/ephemera/vtc64.jpg|

I have tried the &"""" solution as included in this workbook. Also have tried CHAR(34), &quot;, any number of double quote marks by themselves, and probably a few other solutions I have come across. without success. Like I said I seem to either end up with too many quote marks, or not enough...

Thanks,
Stan...

Hello,

Is there a way to affect the default formatting of information when it
is copied from an outside source and pasted into an Excel 2000 spreadsheet?

I recently copied some information from an HTML table for pasting into
Excel. The table contains a list of part numbers, some of which begin
with zeroes, others of which contain a few letters. In any instance
where the part number begins with a zero and has no letters, any leading
zeroes are truncated and the number is formatted as a number rather than
text.

Other data fields are misinterpreted as dates when pasted into Excel.

I would like to prevent this automatic formatting.

Is there a global default setting to Excel that I can change, or is
there any sort of "pre-preparation" I can make to a spreadsheet before
pasting into it?
--

Sincerely,
Don Hicks
Portland, Oregon

Does anyone know of a way to copy cells out of Excel and paste into Notepad using VBA? I can get notepad open, but I can't get the information to paste into Notepad.

Any help would be appreciated.

Hello,

Is there a way to affect the default formatting of information when it
is copied from an outside source and pasted into an Excel 2000 spreadsheet?

I recently copied some information from an HTML table for pasting into
Excel. The table contains a list of part numbers, some of which begin
with zeroes, others of which contain a few letters. In any instance
where the part number begins with a zero and has no letters, any leading
zeroes are truncated and the number is formatted as a number rather than
text.

Other data fields are misinterpreted as dates when pasted into Excel.

I would like to prevent this automatic formatting.

Is there a global default setting to Excel that I can change, or is
there any sort of "pre-preparation" I can make to a spreadsheet before
pasting into it?
--

Sincerely,
Don Hicks
Portland, Oregon

Hi,
I been trying to find a way to remove double quote on null column on my CSV file but can not find any example. Is there anyone out there that can help me? Any help is appreciated.. Thanks.

When I try to save as a .txt file by macro, I get extra " (double quote marks) around my text fields. Is there anyone help me to remove them?
This is my macro:
=============================================
Workbooks.Add
Sheets("Foglio1").Select
Sheets("Foglio1").Name = "Risultato"

For Ind_Tab = 1 To Max_Tab Step 1
Cells(Ind_Tab, 1).Select
ActiveCell.FormulaR1C1 = Tabella(Ind_Tab)
Next Ind_Tab

MsgBox "Stop Run"

ChDir "G:DibeneHTMLWalter Home PageTables"
ActiveWorkbook.SaveAs Filename:= _
"G:DibeneHTMLWalter Home PageTableserp_anagrafica_dipendenti.txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWindow.Close
=============================================
and this is the result in the .txt file:
=============================================
Database ElsagERP
# Database : `ElsagERP`
# --------------------------------------------------------
#
# Struttura della tabella `erp_anagrafica_dipendenti`
#
DROP TABLE IF EXISTS erp_anagrafica_dipendenti;
CREATE TABLE erp_anagrafica_dipendenti (
" AD_Matricola int(11) NOT NULL default '0',"
" AD_Cognome varchar(40) NOT NULL default '',"
" PRIMARY KEY (AD_Matricola),"
" UNIQUE KEY AD_Matricola (AD_Matricola),"
KEY AD_Matricola_2 (AD_Matricola)
) TYPE=MyISAM;
#
# Dump dei dati per la tabella `erp_anagrafica_dipendenti`
#
"INSERT INTO erp_anagrafica_dipendenti (AD_Matricola,"
" AD_Cognome) VALUES'),"
(91, 'Abrate');
=============================================
Thanks in advance.
Wdb

I have to E-mail some stuff out and dont want to attatch a spreadhseet file.
The text I want to export is diffent lengths and when I paste it into notepad or into E-mail I loose the column formatting, how can I maintain this.
I.e. In Spreadsheet (Which obviously is in nice straight columns)Name Trip Num
Joe Smith 123456
Johnathan Anderton 7891011

When I paste into E-mail or notepad
Name Trip Num
Joe Smith 123456
Johnathan Anderton 7891011 (Ends up another tab space to the right)

I have data in an excel worksheet. I have to save it as a .CSV file (comma separated values) to supply the data in ASCII format. A requirement is that alphanumeric fields must be displayed in "double quotes" and numeric not. Excel automatically will put double quotes (when you open the CSV file, say, in Wordpad) only around a text string containing a comma. I have several fields/cells which must be alphanumeric but obviously do not have commas in the text. How do I get the text cell to either (1) display with double quotes in excel (without having to phyically type them into each cell - there are thousands!) or to display with double quotes when opening the file as a CSV or text file.
HELP

A1=hello, A2=world

Both of the following formulas:
=concatenate(A1,char(10),A2)
=A1&char(10)&A2
return

"hello
world"

when pasted into a text file. Is there a way to get rid of the double
quotes? For what it's worth, char(13) gives the same results.

TIA.

Hello there,

I have a spreadsheet into which I load daily data using a macro. There is
data for about 50% of the codes each day.
First, I test whether there are any new codes, and add those to the bottom
of my list.
Then I load the data using the VLOOKUP simplified as follows-

= if(ISERROR(),"",()
NOTE:- There is no space between the two double-quotes.

Then - "Copy" - "Paste Special-Values" - to remove the formulas.

Then I was playing around and noticed that-

-"Edit" -"Go To..." -"Special" -"Blanks"

only selected the earlier empty cells for new codes added, but not for old
codes with empty cells.

I changed the macro to ".ClearContents" for all empty cells created by the
VLOOKUP formula above.

The result was that I was able to select all blanks using the "Go
To..."-etc- "Blanks" command shown above.
More astonishingly my file size reduced from 8 megabytes to 4 megabytes.

This is the reason for my question - "Can blank cells created using empty
Double-Quotes not be empty?"

regards,

JohnI

hi

I need some help here,
assuming value in cell S4 is 11 and value in cell S5 is 47, I've created this formula


the problem is, excel reads this formula like this


there is a double quotation mark (""), which is act like a string, so I can't get a summary from this formula. My question is, how to remove this quote mark so the excel read the formula as


Thanks for your help,
Regards,

Note : sorry I forgot to finish the subject title, it's my mistake

When pasting an Excel table into word - need to past as Excel not table or image - the text is truncated in each cell. Windows XP, Excel 2003, Wrod 2003. Any thoughts?

I have a report in notepad in .txt format. There is a variety of information
arrayed in a variety of tables in the text document. I know that I can paste
the information into excell and each row in the txt document will occupy a
single cell in excell. Is there any way to take that pasted information and
then break it out into multiple cells. I already know that if I put <tabs>
between the values that they will occupy seperate cells but there are a
goodly number of such report and several hundred records per report so a way
to automate this would be very welcome.

Example:

The Text document has A-B-C-D-E-F-G as a row of data. When pasted into
Excell I get A-B-C-D-E-F-G in a single cell. What I would like is to be able
to automate breaking that information out into multiple cells so that I have
<A><B><C><D><E>, etc... in seperate cells.

I have the professional edition of Office 2003, and have developed a weird
problem. If I use any color other than black for a line item in Excel, it
pastes into both Word and PowerPoint as black. I can see the color in Excel
(red, white, etc.), but the properties are not retained when pasted as Paste
Special/Enhanced Metafile. Any suggestions??

Hi I am getting a cell to include double quotes " - but when I copy to clipboard it comes out a bit wrong.

My source:

It should display: 

"  elseif formatdatetime(now,2) =  "11/04/2011" Then

                Response.Write("Message here?")
But instead pastes like this:

"  elseif formatdatetime(now,2) =  ""11/04/2011""
Then

                Response.Write(""Message here?"")

"
How do I format it correctly? what am I doing wrong?

Thanks

Dear all,

Although im an experienced user of excel VBA for certain purposes (functions) e.g. monte carlo simulation, numerical integration etc. I am new to object oriented programming so i urge your patience. I have a piece of code i am writing which will ultimately become an excel add in.

The puropse of the code:
Is to (with an existing blank workbook open) open other reference workbooks , filter the data in those workbooks according to a certain criteria, copy the results, paste into the existing workbook and finally to close the referenced workbook.

via cheating and recording a macro i have been trying to develop the following code (which will then be looped in order to do the same proceedure for several input files and paste the results as different sheets in the existing workbook)

One version of this code worked fine when pasting into another sheet in a workbook, but im having dificulties selecting the data in the reference workbook (the one i didnt have open in the first place!):


	VB:
	
 temp1() 
     
     'dates for files
     'Dim strDate0 As String
     'Dim strDate1 As String
     
    Dim WbFailed As Workbook 
     
    strDate0 = Format(Date - 3, "yyyy.mm.dd") 
    strDate1 = Format(Date, "yyyy.mm.dd") 
     
    Application.ScreenUpdating = False 
     
    Set wbReport1 = ActiveWorkbook 
     
    Workbooks.Open filepath & "2006.10.24 Failed.xls" 
    Set WbFailed = ActiveWorkbook 
     
    WbFailed.Activate 
    Sheet1.Activate 
     'filter the data
    Range("A:AG").Select 
    Selection.AutoFilter 
    Selection.AutoFilter Field:=4, Criteria1:="=3ECL", Operator:=xlOr, _ 
    Criteria2:="=NYCX" 
    Selection.AutoFilter Field:=9, Criteria1:="=USD", Operator:=xlOr, _ 
    Criteria2:="=EUR" 
    Selection.Copy 
     
    wbReport1.Activate 
    Sheet1.Cells(1, 1).Select 
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ 
    False, Transpose:=False 
     
    WbFailed.Close 
     
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
wbfailed opens yet im not selecting the data from there!

Any indication as to the problems with the paste operation/ wether the autofilter mode in excel can be used in such a way / logical or syntax errors in the above is much appreciated.

thanks for your time

faisal

Ok seems simple enough but can't seem to find a solution. I have some cols of data which I'd like to copy but I want the values enclosed in double-quote marks i.e.
A1234 -> "A1234"
The columns of data will be updated continously so I need a formula that can put in the quotes. If I do something like ="B1" Excel thinks the quote marks are for formatting and I only get the B1 value without quotes.
After some processing the quoted cols are clipped to a text file for processing in another prgm -which requires quotes as a delimiter.
Any ideas on a formula for this?
Thanks
Ron.

We have an application we use (MAS 500) that imports CSV files. We use

Excel to create these files; however, since the last upgrade of our
application, we are now required to have the header in each CSV file
use the following format:

"@HDR","BATCH","20060127","Daily 1/27/2006",""
"@HDR","JOURNAL","","1"

The problem is getting the quotation marks right. It will look fine in

Excel, but no matter what I try, it won't give me the format above.
Instead, the CSV file will have either no quote marks or double quote
marks.

I know that doubling up on quote marks is part of Excel's process of
converting to CSV, and I understand why that is useful in most cases,
but in this case it is just the opposite.

Any suggestions?

Hi,

This is my first time on here so I hope someone is able to help me.
I have a workbook with multiple worksheets. I need to copy data from the first 11 columns of 16 of the worksheets & paste the values in a 'loading' worksheet. The worksheets that I need the data from have a similarity in that they are named '**** Pre Load'. Column 6 of the 11 columns is empty but I need the blank column in the loading file. When pasted into the loading file I need it so that the first sheets data will be pasted in cell A2:K10, for example, the next sheet will be from A11:K25 etc.

If anyone can help I would be extremely greatful.

Thanks,
Rod

Hello,

I am struggling with this one. Here is code I use in a SQL script to post a journal in our Everest software. I am trying to create VBA code in an Excel spreadsheet so I can give that file to accounting and let them enter the journal number and post it themselves. I use the query analyzer and this script runs fine. So my issue is not the code so much as it is the converstion from a SQL script to code in the Excel VBA file. I am sure it is a problem with the single versus double quote marks.

Attached code thru notepad here, as it was not allowing me to save it in the thread.
http://www.excelforum.com/attachment...6&d=1333619520

This is the vba code I have in the Excel spreadsheet. I have done some of this before so am familiar with it. I am pretty sure there is something to do with the ' characters and all the " characters that is messing it up.

Code:

sSql = sSql & "exec sp_executesql N'INSERT INTO "EVEREST_Papio".."TRANHIST" "TRANHIST_DATE","USER_ID","FUNC_ID","DESCRIPT") " & vbCrLf
sSql = sSql & "VALUES (@P1,@P2,@P3,@P4)', N'@P1 datetime,@P2 varchar(3),@P3 int,@P4 varchar(27)', " & vbCrLf
sSql = sSql & "@posttime, 'SUP', 9008, @journalstring " & vbCrLf

It gives me an error, Compile Error Expected: End of Statement. That error comes from the first line where all the double quotes are naming the db, table and fields.

Any help is greatly appreciated!

Thanks, Charlie

The below finds, copies and pastes data into a new sheet but NOT with the same column widths. I'm really rusty w/ my VBA and need help adding to this code to make it RETAIN the column width parameters it sees on the source sheet (Agile_Capture_Log) when pasting into the empty sheet (Output_Report).

THANKS so much! Working over the holiday to have this ready for boss by Mon.
Chris

Sub MoveIt()
    Dim LastRow As Long
    Dim r As Long
    Dim s As Long
    Dim oCell As Range
    LastRow = Sheets("AGILE_CAPTURE_LOG").Range("A65536").End(xlUp).Row
    For r = 1 To LastRow
        ' Search for the word "BP1" in the r-th row
        Set oCell = Sheets("AGILE_CAPTURE_LOG").Rows(r).Find(What:="BP1", _
            LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
        If Not oCell Is Nothing Then
            ' If found, increase target row number
            s = s + 1
            ' And copy source row to target row
            Worksheets("AGILE_CAPTURE_LOG").Rows(r).Copy _
                Destination:=Worksheets("OUTPUT_REPORT").Range("A" & s)
        End If
    Next r
End Sub


Hello all,

I have an extremely long (1440 characters) formula that I'm trying to add to a VBA script to paste into a range of cells. The formula itself works fine in a spreadsheet, and the VBA script to select my cell range and paste into the cells also works fine. The problem is that whenever I try to paste my formula into my script, I get an error.

It would seem that there is a maximum character length to lines in VBA, which is too low to accomodate my whole formula. Can someone please confirm if that is the case and if so are there any workarounds?

I considered breaking the formula apart into text strings, pasting them into empty cells, then concatenating the contents of the cell back into a single string and pasting that string into my range of cells, but I couldn't make that work either. And of course it would be nice if I didn't have to go that route.

Here is my VBA script:
Sub Last_cell()
 Range("B1").Select
 Selection.End(xlDown).Select
 ActiveCell.Offset(0, -1).Select
 Range(ActiveCell, Cells(2, 1)).Formula = "[Formula]"
End Sub
And here is my unwieldy formula formatted with double quotation marks for pasting into my script:
The formula itself is basically irrelevant because it works fine on its own. Only the length of it seems to be
significant.

Thanks in advance.

i have a formula composed of 3 lines in one cell:

=" IF c_MSISDN_NDC" & $E3 & " IS NOT NULL THEN
utl_file.put_line (l_file_handle, '
'||c_pkg||LOWER(l_curr_table)||'(c_new_point +" & $E3 & ", 1,
c_oper_name);');
utl_file.put_line (l_file_handle, '
'||c_pkg||LOWER(l_curr_table)||'(c_new_point +" & $E3 & ", 2,
c_oper_name);');"

this produces a text string, e.g. as below:

" IF c_MSISDN_NDC70 IS NOT NULL THEN
utl_file.put_line (l_file_handle, '
'||c_pkg||LOWER(l_curr_table)||'(c_new_point +70, 1, c_oper_name);');
utl_file.put_line (l_file_handle, '
'||c_pkg||LOWER(l_curr_table)||'(c_new_point +70, 2, c_oper_name);');"

when i try to copy/paste it into a text editor, i get the following:

" IF c_MSISDN_NDC69 IS NOT NULL THEN
utl_file.put_line (l_file_handle, '
'||c_pkg||LOWER(l_curr_table)||'(c_new_point +69, 1, c_oper_name);');
utl_file.put_line (l_file_handle, '
'||c_pkg||LOWER(l_curr_table)||'(c_new_point +69, 2, c_oper_name);');"

as you can see there are unwanted double-quotes at the beginning and
end of the line. this is nothing to do with the texteditor as i get the
double-quotes in notepad also (but there i dont get the linebreaks i
need so i'm using an alternative).

i also tried copy/paste-as-special the results of my formula to another
column, then copy/pasting that new column. same result as above, still
double-quotes. this issue only seems to occur where a cell contains
linebeaks.

any ideas how to not get the double-quotes????

--
[please reply to group]


No luck finding an answer? You could always try Google.