Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Ms Excel - Applying Format Changes To Imported Data


  • Please log in to reply
1 reply to this topic

#1 ordieth117

ordieth117

  • Members
  • 3 posts
  • OFFLINE
  •  
  • Local time:12:58 AM

Posted 14 April 2007 - 07:35 PM

In MS Excel 2003, I imported data as text, then afterwards applied a format change to the cells to currency. But the display (nor interpretation) doesn't show up as currency unless I edit the value of the cell and hit enter.

How can I apply the change from text to currency without having to edit each individual cell?

example:
text1.txt has "101.2"

text1.xls imports 101.2 as text into $a$1
Format cell $a$1 to currency, still shows 101.2
cell $a$2 has function =sum(a1:a1), shows 0
Enter cell $a$1 for editing, then hit enter, now shows $101.20
cell $a$2 shows $101.20

My problem is that I have a number of cells that makes entering each one unacceptable.
The available import formats are general (may cause problems temporarily, due to import source), text, and do not import.

Any help?

BC AdBot (Login to Remove)

 


#2 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,577 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:01:58 AM

Posted 14 April 2007 - 08:58 PM

When you import the text file, numbers should become numbers. If they are not, please, set the column where the $ values should be to be either General or numbers. Then the $ format should work and you can increase the number of digits after the decimal point for the entire column. That might be easiest under the 'number' property where you'd specify 2, but I'm not sure that's really needed.

Excel2003 has a somewhat helpful thing - it can show possible errors if enabled in Options. Such that a little triangle shows on the cell, which might say something to the effect 'this looks like text, should we treat it as a number'. See if that helps.

I think the only way Excel would see 101.2 as text is if there was a character, even space, before the number and you're not parsing on spaces and suppressing repeated spaces. I'm not sure what $ would do in front of a number. Perhaps that's the problem, in which case in Notepad just replace $ by nothing.

What other things are you importing? It might help if you showed an example of a row or two of data. Now, the forum format will probably destroy tabs or multiple spaces, so try to describe it as well as you can.

I just gave Excel a file containing
xxx 101.2
zzzzzz 102.30
where I have one space just before the number. I told the importer to split on space, and indeed, the second column became General, with both values being 101.2 and 101.3. In C1 I added 1 to B1, and the result was a number. Then I asked to format column B to Currency, it automatically made the values into 101.20 and 102.30 with $ in front. So, I can't replicate your problem.

Probably, because I misunderstood your description someplace :thumbsup:

Edited by tos226, 14 April 2007 - 09:00 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users