Changing Text to Numerical Value 3

[CandyS]

Prices in a list changed from numerical to text format upon extraction from a database.

I tried both Copy/Paste Special/Add and Multiply, but that only added or multiplied the numbers upon themselves and didn't just change the format to numerical.

How can I change these text values back to numerical?

Thanks,

Candy

 

[mscallisto]

Is this in reference to an excel spreadsheet?

If yes, you can highlight rows or columns and click Format then Cells and pick numeric or whatever

 

[SkipVought]

Candy,

If you "tickle" each value (F2) your digits will be converted to numbers.

'select the column
with activecell
  for each c in range(cells(.row, .column), cells(.row+.rows.count-1, .column))
    with c
      .value = .value
    end with
  next
end with

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

No you can't - Simply using the Format option will not coerce the data back to numeric. The usual route is the one you say you have tried, but you must have done it incorrectly. You are supposed to copy an EMPTY cell, select all the data and then do Edit / Paste Special / Add. This will coerce the data back to numeric.

You can also put a copy of Dave McRitchies Trimall macro in your personal.xls, then just select the data and run the macro. Available here:-

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

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[SkipVought]

oops...

'select IN the column
with activecell.currentregion
  for each c in range(cells(.row, activecell.column), cells(.row+.rows.count-1, activecell.column))
    with c
      .value = .value
    end with
  next
end with

sorry

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

rotflmao:-

Obviously my "No you can't" was not aimed at Skip's note - Must have been typing at the same time

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[SkipVought]

Ken,

I knew that you were NOT referring to my post LMAO!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[CandyS]

mcastillo, Skip, Ken:

Thanks for all your help. Thanks, m--I forgot about the Format "pallette" (if that's what you call it) and I did use it to format a pesky ratio I was working on. I used Ken's copy empty cell/paste special/add and it worked perfectly (I knew I forgot a step somewhere!). However, because this is going to be a template I'll also set it up with Skip's VBA code.

Skip--how does one tickle a number (I usually want to throw them out a window!)? After the code is inserted what do I need to do to make it work?

Thanks!

Candy

 

[mscallisto]

Obviously he was picking on ME because I gave such a BOGUS answer!!

I know you can't do that.....I must have been "coerced" by a devil keyboard to say you can!!

 

[SkipVought]

Candy,

Numbers have extremely small armpits,

so...

tickling a number can be...

ticklish!

But, if you [F2] [Enter], you have accomplished the dirty deed!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

The link to Dave's routine that I gave you will clear out all manner of garbage that can embed itself in your data when importing from external sources. It's one of the most useful routines I have in my personal.xls

Regards
Ken...........

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[JTuc]

Skip,
I have a problem similar to Candy's. I have a bunch of shop order numbers that are formatted like "400000148" that get formatted as "4E+08" when I copy from one column and paste into another. When I use your tickle trick with the F2 key this works. Is there a way for me to do a whole column of cells without having to do the F2 trick on each individual cell?
Thanks,
Jason

 

[SkipVought]

Sure JTuc,

Just use the macro I posted above.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com