removing space and " ' " from exported excel spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a database for my team, which has a command button. This will
export out a query into excel spreadsheet into our shared drive. The problem
is that when it is exported out, the data contains a space and " " " . You
will not see it unless it is clicked on that cell. For example: it will say
" 'Customer Name". This is causing problems when it is combined with
spreadsheet the team used prior to Database. (it doesn't contain space or " '
" ). When two spreadsheet is combined and sorted, it sorts the spreadsheet
produced from the database first, then the original spreadsheet. Is there
any way to have it exported out without those space and " ' " " ? Please
help. Thank you!
 
Hi Georgia,

Please can you explain more carefully. You say variously
" ' "
" " "
" 'Customer Name"
and
" ' " "
and I don't understand what you're seeing.

When you export to Excel it is normal for values in text fields to be
prefixed with an apostrophe, e.g. if a record in Access contains
Georgia
(with no spaces) it will be exported as
'Georgia
(again with no spaces). The purpose of the apostrophe is to force Excel
to treat the data as text. In my experience it doesn't affect the way
the data is sorted.
 
oops, i'm sorry typo.
it's suppose to be " ' " infront of the data.
like: " 'Customer Name".
Thank you
 
You have still posted two different versions. Please can we get this
straight. Is it

(1) | ' Customer Name| (space apostrophe space data)

or

(2) | 'Customer Name| (space apostrophe data)

or

(3) |'Customer Name| (apostrophe data)

or something else?

I'm finding this confusing, because the prefixed apostrophe, as in (3),
is normal and doesn't affect the sort order in Excel. If there is a
space before the apostrophe, as in (1) and (3), both the space and the
apostrophe are visible in the worksheet (and you say they are not
visible until you click on the cell).

Questions:

1) please post the SQL view of the query you are exporting.

2) export a table to a new workbook. Does the problem you have been
seeing in this workbook appear there?

Workround:
To work round the problem you could just use an Excel macro to strip off
any leading spaces, e.g. this air code:

Sub StripLeadingSpaces()
Dim C As Excel.Range

For Each C in ActiveWorkbook.ActiveSheet.UsedRange
C.Formula = Trim(C.Formula)
Next
End Sub
 
Please do the following:

1) please post the SQL view of the query you are exporting.

2) export a table to a new workbook. Does the problem you have been seeing
in this workbook appear there?
 
1.
SELECT tblDearComp.Category, tblDearComp.[Model Number], tblDearComp.[Serial
Number], tblDearComp.[IOSC Trx Num], tblDearComp.[Trx Date],
tblDearComp.[Dealer Name], tblDearComp.[IOSC Cust Num], tblDearComp.[Contract
Num], tblDearComp.[IOSC Cust Name], tblDearComp.InterfaceType,
tblDearComp.[Trx Amount]
FROM tblDearComp
WHERE (((tblDearComp.InterfaceType)="LB") AND ((tblDearComp.[Trx Amount])>0))
ORDER BY tblDearComp.[Trx Amount] DESC

2. yes
 
Thank you. This is interesting. Please do the following:

1) Create a new blank database.

2) Create a table with the following fields
ID - Autonumber, primary key
XX - Text (50 characters)
Save the table as Table1

3) Enter a few records into the table, e.g.
1 Jack
2 Jill
3 Went up the hill
4 999

4) Export the table to a new Excel workbook and tell me whether column B
contains
| 'Jack|
| 'Jill|
(i.e. space-apostrophe-data) or just
|'Jack|
|'Jill|
with the apostrophe only visible in the formula bar or if you double-click
in the cell.

If you get "space-apostrophe-data", I'd be interested to look at the test
database; please email it to me (after removing the spurious middle name
from my email address).


GEORGIA said:
1.
SELECT tblDearComp.Category, tblDearComp.[Model Number],
tblDearComp.[Serial
Number], tblDearComp.[IOSC Trx Num], tblDearComp.[Trx Date],
tblDearComp.[Dealer Name], tblDearComp.[IOSC Cust Num],
tblDearComp.[Contract
Num], tblDearComp.[IOSC Cust Name], tblDearComp.InterfaceType,
tblDearComp.[Trx Amount]
FROM tblDearComp
WHERE (((tblDearComp.InterfaceType)="LB") AND ((tblDearComp.[Trx
Amount])>0))
ORDER BY tblDearComp.[Trx Amount] DESC

2. yes

John Nurick said:
Please do the following:

1) please post the SQL view of the query you are exporting.

2) export a table to a new workbook. Does the problem you have been
seeing
in this workbook appear there?
Click to expand...
Click to expand...
 
Back
Top