Format numbers as "Number"

[Martyn Tindall]

I've got a column of numbers in a downloaded 'phone bill that seem to
be "locked" in "General" format ( aligned at the left-hand side of the
column). How can I change them to numbers that I can use in
calculations?

Excel 97 SR-2

TIA,

 

[joeu2004]

I've got a column of numbers in a downloaded 'phone bill
that seem to be "locked" in "General" format ( aligned at
the left-hand side of the column).  How can I change them
to numbers that I can use in calculations?

If they are left-aligned, they are probably numeric text, not actual
numbers. You can confirm that by using TYPE(A1) and seeing if it
returns 2.

You need to convert the numeric text to actual numbers.

Unfortunately, I am not familiar with Excel 97 per se. But perhaps
one of the following approaches will work for you.

1. Use the Text To Columns feature, if XL97 has it. In XL2003, select
the column of cells, then click on Data, then Text To Column, and
follow the "wizard".

Or....

2. Use Paste Special > Multiply, if XL97 has it. In XL2003, put 1
into an unused cell that is formatted as General, and copy it. Then
select the column of cells to be converted, right-click (or click on
Edit) and click on Paste Special, select Multiply, and click on OK.
You can now delete the 1.

Or....

3. Format a parallel column of cells as General, and enter the formula
=1*A1 and copy down the column, assuming that the cells to convert
start in A1. Copy the parallel column of cells, then select the
original column of cells, right-click (or click on Edit) and click on
Paste-Special > Values and Number Formats > OK.

 

[Martyn Tindall]

If they are left-aligned, they are probably numeric text, not actual
numbers. You can confirm that by using TYPE(A1) and seeing if it
returns 2.

You need to convert the numeric text to actual numbers.

Unfortunately, I am not familiar with Excel 97 per se. But perhaps
one of the following approaches will work for you.

1. Use the Text To Columns feature, if XL97 has it. In XL2003, select
the column of cells, then click on Data, then Text To Column, and
follow the "wizard".

Or....

2. Use Paste Special > Multiply, if XL97 has it. In XL2003, put 1
into an unused cell that is formatted as General, and copy it. Then
select the column of cells to be converted, right-click (or click on
Edit) and click on Paste Special, select Multiply, and click on OK.
You can now delete the 1.

Or....

3. Format a parallel column of cells as General, and enter the formula
=3D1*A1 and copy down the column, assuming that the cells to convert
start in A1. Copy the parallel column of cells, then select the
original column of cells, right-click (or click on Edit) and click on
Paste-Special > Values and Number Formats > OK.

Thanks for the reply. I tried your first two suggestions without
success, but when I tried Method 3, Excel wouldn't accept "3D1" in the
formula. By the way, this problem doesn't appear in my two latest
bills, only in earlier ones; and then only with one column ("Cost Of
Call"). The "Call Duration" column next to it (formatted Custom;
hh:mm:ss) works fine! The data begins on Row 8 and "Cost..." is
Column G (Actually, the first six rows could be deleted - Row 7 is the
header row).

Martyn

 

[joeu2004]

3. Format a parallel column of cells as General, and enter

the formula =3D1*A1 and copy down the column [....]
when I tried Method 3, Excel wouldn't accept "3D1" in the
formula.

My response is getting munged when you view it. The "equal 3D" should
be a single equal sign. In other words, the formula is: "equal one
times A1".

By the way, this problem doesn't appear in my two latest
bills, only in earlier ones; and then only with one column
("Cost Of Call").  The "Call Duration" column next to it
(formatted Custom; hh:mm:ss) works fine!  The data begins on
Row 8 and "Cost..." is Column G (Actually, the first six rows
could be deleted - Row 7 is the header row).

The problem might depend on how you import or enter the data into your
worksheet and/or the format of the cell(s) before you import or enter
the data.

 

[Martyn Tindall]

3. Format a parallel column of cells as General, and enter

the formula =3D3D1*A1 and copy down the column [....]
when I tried Method 3, Excel wouldn't accept "3D1" in the
formula.

My response is getting munged when you view it. The "equal 3D" should
be a single equal sign. In other words, the formula is: "equal one
times A1".

OK, I did a parallel column and copied that formula all the way down
(substituting G8 for A1 in the top cell), but they all turned to
#VALUE!. Paste Special, Values just produced a column of #VALUE!.

Martyn>

 

[joeu2004]

OK, I did a parallel column and copied that formula all
the way down (substituting G8 for A1 in the top cell), but
they all turned to #VALUE!. Paste Special, Values just
produced a column of #VALUE!.

I suspect your text cells have some nonprinting characters, probably
the non-breaking space (HTML   -- ASCII 160).

For details, refer to
http://office.microsoft.com/en-us/e...-beginning-and-end-of-a-cell-HP003056131.aspx.

But the following might resolve both problems:

=1*SUBSTITUTE(A1,CHAR(160),"")

Since you had problems viewing my previous response, I will spell that
out:

equal one times SUBSTITUTE left-parenthesis A1 comma CHAR(160) comma
double-quote double-quote right-parenthesis.

If that does not resolve your problem, I suggest that you make your
Excel file -- or an example file that demonstrates the problem --
available. Two ways to do that:

1. Upload it to a file-sharing web site and post the URL here. Be
sure to explain where to look for the problem in the Excel file. The
following is a list of free file-sharing web sites that people have
suggested elsewhere.

Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
Box.Net: http://www.box.net/files

Or....

2. Send the Excel file to me directly. Send to joeu2004 "at"
hotmatil.com.

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

 

[Martyn Tindall]

I suspect your text cells have some nonprinting characters, probably
the non-breaking space (HTML   -- ASCII 160).

For details, refer to
http://office.microsoft.com/en-us/excel-help/remove-spaces-from-the-beginni=
ng-and-end-of-a-cell-HP003056131.aspx.

But the following might resolve both problems:

=3D1*SUBSTITUTE(A1,CHAR(160),"")

Since you had problems viewing my previous response, I will spell that
out:

equal one times SUBSTITUTE left-parenthesis A1 comma CHAR(160) comma
double-quote double-quote right-parenthesis.

If that does not resolve your problem, I suggest that you make your
Excel file -- or an example file that demonstrates the problem --
available. Two ways to do that:

1. Upload it to a file-sharing web site and post the URL here. Be
sure to explain where to look for the problem in the Excel file. The
following is a list of free file-sharing web sites that people have
suggested elsewhere.

Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
Box.Net: http://www.box.net/files

Or....

2. Send the Excel file to me directly. Send to joeu2004 "at"
hotmatil.com.

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

Thanks, I'll look at your suggestions later today.

Martyn