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?
Full Version: Ms Excel - Applying Format Changes To Imported Data
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
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.