when is a number not a number? 1

[hopelessliar]

I sometimes have to import data nito excel from other apps, and they often produce columns of things that look like numbers but aren't. So I can't sum them etc. I know that I can go to each individual cell and press F2 and this converts it to something I can use but this is more than a little bit inconvenient on 2000+ cells.

So, what are the cells when they're not numbers?
Does anyone know how to convert them 'en masse'?

Any help greatfully appreciated.

 

[SkipVought]

Hi,

If a string of numeric characters are imported as text, you have a string of numeric characters instead of a number.

The spreadsheet solution is to create a new column, assuming that you numeric string is in col A...

=VALUE(A1)

copy down
copy the column
select col A
Edit/Paste Special = Values

A macro solution. Paste this code into a VB Module (alt+F11)

Sub TickleValues()
  With Selection
    With .CurrentRegion
      r1 = .Row
      r2 = r1 + .Rows.Count - 1
    End With
    c = .Column
  End With
  For r = r1 to r2
    With Cells(r, c)
      .Value = .Value
    End With
  Next
End Sub

Select any cell in the subject column and run this macro.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

another couple of manual solutions

Enter a 1 into any blank cell and copy it
Select your "Textual Numbers"
Edit>PasteSpecial>Value & TICK MULTIPLY

OR

Enter a 0 into any blank cell and copy it
Select your "Textual Numbers"
Edit>PasteSpecial>Value & TICK ADD

OR - if you have XP

Go Tools>Options>Error Checking and make sure "Numbers Stored As Text" is ticked
Select the set of textual numbers
Go back to the top using the scroll bars
You should see a little box next to the top cell - click it
there should be a list of options - one of which is "Convert To Number".....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[hopelessliar]

Perfect. Hugely helpful as ever. Thanks guys.