MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Non-text to text conversion.


Posted by Dreamboat on February 14, 2002 5:54 PM

I need a formula that converts the following number (which is a text format) to a number value (not text format). Please don't offer the x1 fix. I need a FORMULA. Yes, it's homework, but if anyone can at least offer at least some guidance, I'd GREATLY appreciate it!

15-
20-
(where the negative sign is on the RIGHT of the value and, hence, is seen as text)

THANKS!!


Posted by Dreamboat on February 14, 2002 6:09 PM

Woohoo! Figured it out!

=IF(RIGHT(M2)="-","-"&SUBSTITUTE(M2,"-",""),M2)

Posted by Dreamboat on February 14, 2002 6:11 PM

Oops! That's:

=IF(RIGHT(M2)="-",VALUE("-"&SUBSTITUTE(M2,"-","")),VALUE(M2))

Posted by Derek on February 14, 2002 9:17 PM

Re: also =(LEFT(M2,FIND("-",M2)-1))-(LEFT(M2,FIND("-",M2)-1))*2

Posted by anno on February 14, 2002 9:31 PM

or =-LEFT(A1,LEN(A1)-1). this one is ok with any number of characters but fails if the "-" isn't present. it is simpler though and does meet your exercise's parameters

Posted by Aladin Akyurek on February 14, 2002 10:24 PM

Also...


=IF(RIGHT(M2)="-",-(SUBSTITUTE(M2,RIGHT(M2),"")),M2)

Posted by Dreamboat on February 15, 2002 11:23 AM

Wouldn't they both return TEXT?