Thanks, Ron - that's exactly what N Harkawat wrote ... and works just fine.
Thanks for your time to confirm! Cheryl B.
"Ron Rosenfeld" wrote:
On Thu, 26 May 2005 10:02:01 -0700, Cheryl B.
wrote:
I have an account number of 941 486-1072 041025 in text format.
Need to remove spaces and dashes for a continuous data string.
Tried using find/replace to remove spaces and dashes and it works - *almost*
The problem is that the last digit of 5 is replaced by a 0 (zero) and shows
as
9414861072041020
Any suggestions? Other than manually editing each cell, that is?
When you are doing your find/replace and winding up with digits, excel converts
it to a number. Since Excel numbers can only have 15 digits, the 16th digit
gets changed to a zero.
So you need to return a text string and that requires a worksheet function:
=SUBSTITUTE(SUBSTITUTE(A1," ",""),"-","")
--ron
|