Data Convert Text To Number

[Gaffnr]

Hi, I am dumping data from an external system into Excel via a 3rd party
application. When a particular field comes into JDE, its come in as text.

i.e. 00350, 00390. I need these to show as numbers because they are needed
in a numerical formula. I can highlight the full range and click on the
little warning icon that pops up (when the top left of the cell is a tiny
green triangle) and use convert to number. However, I have four different
dumps all with the same issue. Its a pain in the butt to have to do this.
Is there a formula I can use?

Thanks
Rob

 

[yshridhar]

If your data is in A column put B2 = --(a2) or B2 = value(a2).
Copy it down.
best wishes
Sreedhar

 

[Gord Dibben]

Select all and Format to General.

Copy an empty cell and Copy.

Select the range to change and Edit>Paste Special>Add>OK>Esc.

Note: you will lose the leading zeros because the data is now numeric.

If you want them shown use a Custom Format of 00000


Gord Dibben MS Excel MVP