Removing spaces from imported data

Excel 2003

I copied and pasted some "numerical" data from a table 
into an excel worksheet.  I needed to sum up several 
columns worth of data but each time it enters the sum as 
0.

So, after experimenting I found that there is a space 
after each number. I tried using the TRIM and LEFT/RIGHT 
functions and neither worked as far as allowing me to sum 
up the columns. 

When I retype the number, it obviously works.  I tried 
formatting the cells to numbers before and after trying 
the TRIM function but to no avail.

Is there a way to get this data into a useable format and 
if so, how? Any help is certainly appreciated!

Randy
0
anonymous (74722)
11/11/2004 7:26:19 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
682 Views

Similar Articles

[PageSpeed] 14

Hi
try
=--SUBSTITUTE(A1," ","")

--
Regards
Frank Kabel
Frankfurt, Germany

"Randy Lefferts" <anonymous@discussions.microsoft.com> schrieb im
Newsbeitrag news:004901c4c824$51f030d0$a501280a@phx.gbl...
> Excel 2003
>
> I copied and pasted some "numerical" data from a table
> into an excel worksheet.  I needed to sum up several
> columns worth of data but each time it enters the sum as
> 0.
>
> So, after experimenting I found that there is a space
> after each number. I tried using the TRIM and LEFT/RIGHT
> functions and neither worked as far as allowing me to sum
> up the columns.
>
> When I retype the number, it obviously works.  I tried
> formatting the cells to numbers before and after trying
> the TRIM function but to no avail.
>
> Is there a way to get this data into a useable format and
> if so, how? Any help is certainly appreciated!
>
> Randy

0
frank.kabel (11126)
11/11/2004 7:58:10 PM
Hi Randy,
In addition to what you have already done, and what Frank has suggested 
check out:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Trim spaces macro will pick up what CLEAN() and TRIM () miss
Also just to not overlook the obvious, make sure that they are not formatted 
as text.

"Randy Lefferts" wrote:

> Excel 2003
> 
> I copied and pasted some "numerical" data from a table 
> into an excel worksheet.  I needed to sum up several 
> columns worth of data but each time it enters the sum as 
> 0.
> 
> So, after experimenting I found that there is a space 
> after each number. I tried using the TRIM and LEFT/RIGHT 
> functions and neither worked as far as allowing me to sum 
> up the columns. 
> 
> When I retype the number, it obviously works.  I tried 
> formatting the cells to numbers before and after trying 
> the TRIM function but to no avail.
> 
> Is there a way to get this data into a useable format and 
> if so, how? Any help is certainly appreciated!
> 
> Randy
> 
0
mzehr1 (59)
11/11/2004 8:14:02 PM
Thank you for the reply Frank.  Unfortunately it didn't 
work but was worth a shot :)
>-----Original Message-----
>Hi
>try
>=--SUBSTITUTE(A1," ","")
>
>--
>Regards
>Frank Kabel
>Frankfurt, Germany
>
>"Randy Lefferts" <anonymous@discussions.microsoft.com> 
schrieb im
>Newsbeitrag news:004901c4c824$51f030d0
$a501280a@phx.gbl...
>> Excel 2003
>>
>> I copied and pasted some "numerical" data from a table
>> into an excel worksheet.  I needed to sum up several
>> columns worth of data but each time it enters the sum 
as
>> 0.
>>
>> So, after experimenting I found that there is a space
>> after each number. I tried using the TRIM and 
LEFT/RIGHT
>> functions and neither worked as far as allowing me to 
sum
>> up the columns.
>>
>> When I retype the number, it obviously works.  I tried
>> formatting the cells to numbers before and after trying
>> the TRIM function but to no avail.
>>
>> Is there a way to get this data into a useable format 
and
>> if so, how? Any help is certainly appreciated!
>>
>> Randy
>
>.
>
0
anonymous (74722)
11/11/2004 8:31:16 PM
Thank you for the links.  I will head over there and see 
if it will work.
>-----Original Message-----
>Hi Randy,
>In addition to what you have already done, and what 
Frank has suggested 
>check out:
>
>http://www.mvps.org/dmcritchie/excel/join.htm#trimall
>
>Trim spaces macro will pick up what CLEAN() and TRIM () 
miss
>Also just to not overlook the obvious, make sure that 
they are not formatted 
>as text.
>
>"Randy Lefferts" wrote:
>
>> Excel 2003
>> 
>> I copied and pasted some "numerical" data from a table 
>> into an excel worksheet.  I needed to sum up several 
>> columns worth of data but each time it enters the sum 
as 
>> 0.
>> 
>> So, after experimenting I found that there is a space 
>> after each number. I tried using the TRIM and 
LEFT/RIGHT 
>> functions and neither worked as far as allowing me to 
sum 
>> up the columns. 
>> 
>> When I retype the number, it obviously works.  I tried 
>> formatting the cells to numbers before and after 
trying 
>> the TRIM function but to no avail.
>> 
>> Is there a way to get this data into a useable format 
and 
>> if so, how? Any help is certainly appreciated!
>> 
>> Randy
>> 
>.
>
0
anonymous (74722)
11/11/2004 8:31:51 PM
Reply:

Similar Artilces:

Insidious Plot to Normalize Data!! Bwahahaha!!
Hi folks! Sorry for the melodramatic introduction... Here's the thing: I've got a database that relies heavily on a non normalized table. The structure is like this: Order Number is the primary key and there are about 30 fields with information about the particular order that are filled in by Order Entry personnel. There are four fields (and related fields) for entering information about up to four different part numbers per order. I would like to normalize this table with a union query that John Spencer helped me develop so that I can split off the part numbers to anothe...

Importing Contact Groups from Outlook Express6
I am installing new PC's running outlook 2002. I want to import the contacts from the old PC which ran Outlook Express 6.0. How can I keep the contact groups intact? Now I just get 600 indavidual contacts and no grouping. Groups cannot be imported from OE. -- Russ Valentine [MVP-Outlook] "jason" <anonymous@discussions.microsoft.com> wrote in message news:00c301c39cab$5890a140$a301280a@phx.gbl... > I am installing new PC's running outlook 2002. I want to > import the contacts from the old PC which ran Outlook > Express 6.0. How can I keep the contact gr...

How do I remove page numbers in handouts?
I have tried to remove the page numbers in the print preview of a presentation (2007) by the normal options box which includes headers etc. This does not work - is this a bug in the program? If so how do I get it to work? I am trying to send a pdf version to someone. They are using 2003, on which it definitely works. Could be! Deleting the # from the slide number placeholder on the handout master seems to work though. -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "Hil...

Using non-numerical imported data
When importing data from financial websites, some of it comes in as 171.1Mil instead of 171.1 , which means Excel won't recognise it as a figure and I can't manipulate it. Any suggestions? thanks Hi You could use Data/Text to columns to split off the text bit, or use =--LEFT(E14,LEN(E14)-3) to trim it off. Andy. "Ricardinho" <anonymous@discussions.microsoft.com> wrote in message news:0acc01c39309$c0a93810$a401280a@phx.gbl... > When importing data from financial websites, some of it > comes in as 171.1Mil instead of 171.1 , which means Excel > won't r...

How to import an excel document (form) into publisher
I have tried several times to import a form I produced in excell but every time I try it shuts down my publisher document I am importing to. Which versions of the software? What steps are you taking? -- JoAnn Paules MVP Microsoft [Publisher] "Warren" <Warren@discussions.microsoft.com> wrote in message news:DBC5D466-CE90-4657-BDFD-577984D46723@microsoft.com... >I have tried several times to import a form I produced in excell but every > time I try it shuts down my publisher document I am importing to. Have you tried: 1. Copy > Paste from Excel to Publisher...

Where to get Data Migration Framework 3.0
Can someone tell me where to download the Data Migration Framework version that works with MS CRM 3.0? Thanks in advance Dear Samuele, "Samuele Marconcini" wrote: > Can someone tell me where to download the Data Migration > Framework version that works with MS CRM 3.0? See: http://groups.google.com/group/microsoft.public.crm/browse_thread/thread/dfb1fef18647c6e2/ Cheers Arne Janning Thanks Arne. ...

Working in Sheet2 with data from Sheet1
I am working in Sheet2. The data is in Sheet1. I can not get the data in column in the 15th column to initalize the "If" even if Textbox1.Text matches. For k= 1 to 100 If Sheets("Sheet1").Cells(k,15)=TextBox1.Text Then code... End if Next k I tried: If Sheets("Sheet1").Cells(k,15)=Sheets("Sheet2").TextBox1.Text Then With Sheets("Sheet2") If Sheets("Sheet1").Cells(k,15)=.TextBox1.Text Then .... End With If Sheets("Sheet1").Cells(k,15)=TextBox1.Text Then Nothing worked. Philos...

Moving a line chart data point revises data table value in Excel '
I recall a capability I used with Excel years ago that enabled me to create a line chart from a data table, then move the plot points on the chart to "smooth" the line, and the source data chart value would change with the corresponding new plotted point. I'd like to use that capability with Excel 2007 but can't find to activate it. Any power users know how? Thanks! Ed Ed, Microsoft decided that users didn't need that feature. More than likely, they were tired of support calls asking why the values changed when one dragged a data point...... No really, they took i...

Extract unique data
I am using excel 2003 I have a file like below Rate month GBP 1.46 FEB 2000 1.47 MAR 3000 1.47 APR 3500 1.48 MAY 1000 I want to create summary Rate FEB MAR APR MAY 1.46 2000 1.47 3000 3500 1.48 1000 I can use sumif function to return the total, however, how can I insert only the unique rate in the first column automatically. Thanks a lot ! eva cheng Hi, I suppose information is in sheet 1 columns A,B...

Ignore a Blank Data Series in the Legend
Hi, I'm creating Pie Charts based on data from 10 data series fields. Some of the data fields are blank, and so I've managed to create data labels that only show on the Pie Chart if there is a value (otherwise the Chart was too hard to read due to lots of blank labels). However, if I show the Legend, the blank data series field still appear, so I have say, 4 labels with the correct data series names but 6 which are blank. Can I make the Legend ignore blank fields, and just show the 4 that have values? Thanks You can manually remove individual legend entries. Click on the lege...

Migrating data from SalesLogix to MSCRM?
Hi There, Has any one done migrating of data from SalesLogix to MSCRM? We are using Scribe to migrate data, but Scribe has to us that making use of Scribe adaptor would not make much difference since our version of SalesLogix is 5.x. So we are connecting to SaleLogix database using ODBC which is fine. We just want to know any one has been through this experience and give us some helpful information. In particular how we could bring across activites from SalesLogix to MSCRM, since we have limited knowledge of SalesLogix data structure. Regards, Kyaw The SLX data structure is reall...

Protect Data, but allow sorting
Hello, In Excel 2003 is it possible to protect the data in a sheet, but to allow the data to be sorted? In the Protect Sheet command, it indicates that this facility exists, but when I try to do a sort it won't do it until the sheet is unprotected. Pete Make sure that there's an empty column between the range to be sorted and any "adjacent" data. If you have a locked cell butting up to the range to be sorted, excel yells! Pete Walburn wrote: > > Hello, > > In Excel 2003 is it possible to protect the data in a sheet, but to allow > the data to be so...

Extracting XML data to be used in XSLT HTML output
Ok: I have done small level Xml stuff and am just now starting to understand some of the plumbing involved. I've done a lot of .NET programming for SQL Server, but little for Xml data stores. I am currently just interested in using the .xsl file that I have being applied to a source .xml file through ASP classic. Here's what I have: XSL file ---------------------------------------------------------------------------- --- <?xml version="1.0" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output meth...

Automatic import
Hi, Do you know a command line to import data from files ? I just want to do an automatic import from a dbf file every day without any intervention from a user. Is it possible ? Thx I don't think there's a command line for it. You'd either have to write code that uses the API to do what you want or use a button-pressing script to do what you want. "news.microsoft.com" <personne@microsoft.com> wrote in message news:Ou5VnOATGHA.196@TK2MSFTNGP10.phx.gbl... > Hi, > > Do you know a command line to import data from files ? > I just want to do an aut...

How to Use Data Migration Pack
Hi All, i downloaded the DMF from Microsoft.com, i read the documents but i couldn't understand that, My exact Requirement is, i have to import 18000 Records to Product Catalog, what i have to do now, please give me guidelines to import Records, Thanks Thangadurai On Sep 8, 7:04 am, mukathangadu...@gmail.com wrote: > Hi All, > i downloaded the DMF from Microsoft.com, > i read the documents but i couldn't understand that, > My exact Requirement is, i have to import 18000 Records to Product > Catalog, > what i have to do now, please give me guidelines to import Reco...

import contacts to outloook from phone
How do I import contacts on iphone to computer Microsoft outlook? Not an Outlook question. Follow the directions Apple provides. -- Russ Valentine "Kashi" <Kashi@discussions.microsoft.com> wrote in message news:188E347F-8291-47A8-8F3E-72136B196A06@microsoft.com... > How do I import contacts on iphone to computer Microsoft outlook? ...

overflow data
When i input data into a cell, can i fix it so it doesn't overflow the adjacent but empty cell? Thanks. You could enter a space character in the adjacent cell, so it appears empty, but will stop the overflow. Barbara wrote: > When i input data into a cell, can i fix it so it doesn't > overflow the adjacent but empty cell? Thanks. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html thanks, Genius! :-) >-----Original Message----- >You could enter a space character in the adjacent cell, so it appears >empty, but will ...

RE: Use important pack from Microsoft Corp.
--tyvfmhpfkrslgtv Content-Type: multipart/related; boundary="lkryprwpfgi"; type="multipart/alternative" --lkryprwpfgi Content-Type: multipart/alternative; boundary="qytmxtjaaa" --qytmxtjaaa Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "September 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to maintain the security of your computer from these vulnerab...

external data not in a table
I have a worksheet originally created in excel2003 that gets external data from a db2 database. After migrating to excel2007 the external data in the worksheet is not in a table (clicking on a result cell does not make Table Tools appear). When creating a new database query I find that the results always get put in table. I don't want the results in a table. How do I make the results of a new query NOT be a table? And I don't want to break the link to the database since I update the results periodically. ...

5pt space between paragraphs when copied from Word to Publisher
When I copy several paragraphs from a word document and paste it in Publisher I always get 5pts added before and after each paragraph. Is there anyway to change this so I don't have to delete the space each time? I have Office version 2003 and Windows XP. Paste special as unformatted text... or Select all, Format, Paragraph, change the before and after. -- Mary Sauer http://msauer.mvps.org/ "BoKay" <BoKay@discussions.microsoft.com> wrote in message news:0EB4C534-F64D-409D-AC89-37367675C71D@microsoft.com... > When I copy several paragraphs from a wo...

format based on data from another cell
I want the names in column B of my spread sheet to highlight either red or yellow based on the value of the cell in column L, but with conditional formatting you can only format based on the value of the cell you are formatting, as far as i can tell. Hi Sanna conditional formatting can be used to do this, select the column B cells that you want the formatting to be applied to, ensure that the first selected cell is the first line visible at the top of the worksheet choose format / conditional formatting choose formula is type =$L2=x where row 2 is the first row in the selected column an...

Unable to import contacts to new computer
I've exported contacts and e-mails to pst folders on my old computer, when trying to import to Outlook on the new computer I get an error message that access is denied. I'm the administrator on both systems. Neither system appears to have Exchange on them (all articles say to change permission properties in exchange). How do I move my information from one computer to another at this point?? Make sure the .PST file isn't marked read-only. If it's on a CD, move it to your hard drive and remove the read-only attribute. Look here for all the information you need on bac...

Merging data from two worksheets
I have two worksheets with lists on them. On worksheet A there are items we use, and charge code (which correspond to individual items); but there is no RMS code (a specific code we use for our system). On worksheet B there is a larger list of items, many of which are also on worksheet A. Worksheet B has a column with the same charge codes as are used in worksheet A, but worksheet B has the corresponding RMS codes. For example... On A: Item RMS Charge Code Stick (blank) 12345 Ball (blank) 54321 Bases (blank) 21543 On B: Item RMS Charge...

auto generate emails from data stored in excel spreadsheet
Hi, Is there a way in excel to automatically generate an email which is stored in a worksheet either using excel macros or in conjuction with outlook. thanks in advance. Mark K Look here http://www.rondebruin.nl/sendmail.htm Maybe this one http://www.rondebruin.nl/sendmail.htm#message -- Regards Ron de Bruin http://www.rondebruin.nl "MarkK" <anonymous@discussions.microsoft.com> wrote in message news:169b901c447f2$268f26b0$a001280a@phx.gbl... > Hi, > Is there a way in excel to automatically generate an > email which is stored in a worksheet either us...

How do I import a hotmail address book into Outlook?
is it my imagination or has microsoft made it ridiculously difficult to export NINE PAGES OF MY HOTMAIL ADDRESS BOOK into microsoft Outlook?!?! austinjames ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?m...