Changing column to Number stored as Text 2

[jchim32]

I have an excel spreadsheet that I would like to convert the entire column of numbers to 'Numbers stored as Text'. In my research, I have only found how to convert it the other way, from Number stored as Text to Number. If anyone knows how to convert the entire row, please let me know.

TIA,

Jeff

 

[bkpchs237]

jchim32,

If your number is in cell A2 use the following formula =text(A2,"#&quot copy formula as needed to other cells.

Hope this helps.

 

[DaleWatson123321]

Hi Jeff,

Here's one way...

Step 1) In the adjacent column (perhaps insert a temporary column if necessary), enter this formula:
=FIXED(A1,2,0)

The second argument (2 in the above example), is the number of decimal places you want the text to show.

The third argument (0 in the above example), will cause a comma to be used. A "1" will cause no comma.

Therefore, using the above example, if A1 contains the value 12345, the result will be: 12,345.00

Step 2) Copy the formula down to match the number of rows of numbers in your column.

Step 3) Highlight and Copy the formulas.

Step 4) Use: Edit - Paste Special - Values

Step 5) Copy the (text) values to replace the numbers.

Step 6) Delete the column where you had the formulas.

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[jchim32]

Dale,

That worked like a charm, thx

 

[DeLaMartre]

"FIXED", eh? That is a wonderful tip!

I hope everything is well in the Great North Land! -Bob in California

 

[gapiesco]

I have a similar convert problem to solve. I'm trying to take a "time column or cell: formatted as 00:00:00 to convert to just a "mn.0" with a formula applied to result that will compute a percentage. For example: Take 10:00:30 (10 hrs, 30 secs) convert to 600.5 minutes; then dividethat by 44640 (number of minutes in a month) to get a percentage. I've tried =CONVERT (C3,"hr","mn&quotbut end up with answer in format 600:30:00. Any ideas?

 

[DaleWatson123321]

gapiesco,

While your situation is similar, you should preferably post it as a SEPARATE posting.

By adding it to the end of an existing posting that already has been "flagged with a STAR", there's an increase chance that it won't be noticed - by those who could potentially offer a solution, because the STAR indicates that the posting has ALREADY been resolved.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

gapiesco,

Here's a possible solution...

In cell A1, I've entered: 10:00:30

In cell B1, I've entered: =((HOUR(A1)*60)+SECOND(A1)/60)/44640

I've formatted B1 as Percentage - 2 Decimals and the answer I get is: 1.35%.

I hope this helps. Please advise as to how this fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[gapiesco]

Dale, thanks bunches. First of all I apologize for not posting as a new thread - this was my second visit to tek-tips since hearing of it this week.

Here was my final tweak:
=(((HOUR(C3)*60)+MINUTE(C3)+SECOND(C3)/60)/44640)-100. I formatted the cell as a number becuase the percentage reflected downtime, I was looking for uptime.

Is there something I can do to correct my "poor posting?"

 

[xlhelp]

Obviously, I am late, but anyway:

If you multiply c3*1440 you would get your 600.5 minutes and then you could divide it by whatever number you desire

Explanation: Time is measured in days in Excel. So 600 minutes is really 0.42 of the whole day. Mutiplying c3 will give you the minutes since 1440 minutes make up one whole day.