Excel Mac, simply cannot convert "text" numbers to pure numbers

[MSC]

I've tried everything.....

I pasted some datapoints from Gmail into Mac Excel & now want to manipulate them.

I've tried:
-Copy paste special Operation Add, or Multiply.
-Multiplying by 1, or adding zero
-Text to columns
-Outright changing the format
-Even using =VALUE(TRIM(CLEAN(A1))) leads to a #VALUE! result
-Note, The exclamation point does not appear. The numbers are left justified.

I manually changed the first number just to prove I wasn't going crazy......thanks, Mike

 

[awoohaw]

You could have a zero length character (codes 10,13 or 160 and many others). Have you examined text length with the LEN functions, or do a Code(Right(cellref,1)) and see if the value is not a typical numeric character value.

 

[Fluff]

What do you get with

=CODE(MID(A1,SEQUENCE(,LEN(A1)),1))

 

[MSC]

Thanks, when I put
=CODE(MID(A1,SEQUENCE(,LEN(A1)),1))
in the next column, I get

 

[MSC]

You could have a zero length character (codes 10,13 or 160 and many others). Have you examined text length with the LEN functions, or do a Code(Right(cellref,1)) and see if the value is not a typical numeric character value.

Well, I feel a bit silly now - I tried just =LEN and saw there was 1 more character than what was represented by the numbers & decimal. I.E. ......trailing space. Find/replace, good to go. Thank you both for your suggestions that helped spur this solution - much appreciated.

 

[Fluff]

I don't know if Macs use a different system, but for me Code 202 is Ê rather than a space.
But good to know your sorted & thanks for the feedback.

 

[awoohaw]

Well, I feel a bit silly now - I tried just =LEN and saw there was 1 more character than what was represented by the numbers & decimal. I.E. ......trailing space. Find/replace, good to go. Thank you both for your suggestions that helped spur this solution - much appreciated.

I'm glad you were able to find a solution here!