View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default Numbers that are really not numbers?

Hi Craig,

Use David McRitchie's TRIMALL() function which can be downloaded he

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

--
Kind regards,

Niek Otten

"C Brandt" wrote in message ...
| JE:
| Thanks for the suggestion. Tried it but it didn't work for me. The problem
| turns out to be that pesky first non-printable character. I thought
| CLEAN(A1) would work(A1 contains the number/test), it didn't. Eventually I
| tried the RIGHT(A1,9) but the first attempt at this, I miscounted the
| digits and entered RIGHT(A1,10). I was in a deadline crisis mode so I
| manually deleted the first character of around 200 entries, twice. It was in
| later research that I stubbled onto my error and used the correct RIGHT
| formula.
|
| Thanks for the response. I can live with the RIGHT function, just
| disapointed that the CLEAN function didn't do the job.
|
| Regards,
|
| Craig
|
| "JE McGimpsey" wrote in message
| ...
| One way:
|
| Copy an empty cell. Select your "text numbers". Choose Edit/Paste
| Special, selecting the Values and Add radio buttons. Click OK.
|
| In article ,
| "C Brandt" wrote:
|
| Spent about 30 minutes today fixing a column of numbers that I needed to
| act
| like numbers, not text.
|
| 479862673
|
|
|
| 479862673
|
| The first number above has a character in front of it that prevents it
| from
| being treated like a number. If I were to multiply it by one I get the
| #VALUE! error. I deleted the first character and then it acts like a
| number.
|
| I finaly figured out that a RIGHT(text,9) will solve my problem.
|
| Is there a simpler solution?
|
| Thanks in advance,
| Craig
|
|