numbers need to be numbers

[Synthia]

I exported a spreadsheet from a website using their export link, using MS 2007, it has hundreds of cells with numbers, I need to make charts, but, even though the characters look like numbers they are not recognized as numbers.



I tried selecting/changing format. I tried entering 1 as a number in a different cell, made sure it is a number, copied it, and did paste/special/multiply.



The only thing that works is to create a new spreadsheet and hand enter the numbers, and there is not enough time.



Help....? THANKS!

Synthia

[martindwilson]

got a sample?

[Synthia]

got a sample?

SURE, thanks, pasted and attached an abbreviated version.
Synthia

% MHS % MLG % LMLG
BCH 2008-09 66 64 51
BCH 2007-08 68 68 52
BCH 2006-07 58 59 47
GGH 2008-09 34* 52 * 53 *
GGH 2007-08 26* 44 * 42 *
GGH 2006-07 29* 45 * 48 *
GCH 2008-09 58* 58 * 52 *
GCH 2007-08 62* 65 * 54 *
GCH 2006-07 52* 55 * 43 *
IHS 2008-09 22* 41 * 50 *
IHS 2007-08 20* 43 * 43 *
IHS 2006-07 18* 45 * 49 *
LHS 2008-09 35* 49 * 51 *
LHS 2007-08 34* 45 * 38 *
LHS 2006-07 32* 48 * 47 *
LWH 2008-09 29* 41 * 46 *
LWH 2007-08 36* 46 * 38 *
LWH 2006-07 43* 50 * 52 *
NHS 2008-09 54* 52 * 40 *
NHS 2007-08 58* 62 * 47 *
NHS 2006-07 47* 50 * 40 *
PRH 2008-09 44* 52 * 50 *
PRH 2007-08 43* 52 * 48 *
PRH 2006-07 41* 48 * 43 *

[DonkeyOte]

E:G are all number values in your file (ie COUNT(E:G) returns 72 as expected).

If it's D you need to change then highlight Column D and run Text to Columns -> Delimited - step 3 pick Date -> YMD and click Finish.

EDIT: ignore the above... I didn't look correctly !

As JB states replace the space with nothing (ie highlight data E2:G? and run Edit -> Replace replacing Char 160 (ALT + 0160) with nothing)

[JBeaucaire]

You have hidden spaces in your data, the #1 culprit of this problem.

This formula should give an answer of 2, and it gives 3:

=LEN(E5)


You'll need to remove all the spaces.

[Synthia]

trying it now THANK YOU BOTH

[martindwilson]

its the old char 160 problem
select the lot
find/replace
in find hold down the alt key and type 0160 with the numeric keypad leave replace as nothing ,replace all

[Synthia]

OK, I am not as sharp as I wish I were, only an Excel pro-wannabe...exactly how do I (as in the steps) remove the spaces by run Edit -> Replace replacing space with nothing)
..thought I could figure it out but am lost.....
sorry so limited...
but VERY GRATEFUL!
Synthia

[Synthia]

THANK YOU! SOLVED! and STILL - EVEN MORE - SO VERY AMAZED at how kind and helpful the pro's here are!
THANK YOU so much!
Synthia

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.