MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Mixed numbers and text... I need to perform formula on the number


Posted by David Megnin on October 02, 2001 11:45 AM

I have a form with a field for a software release number. The customer may put 1.1 or 6.5 etc. in the field or he may put Ball 1.1 or Block 6.x etc. in the field.
How do I perform a calculation using the number part only? For example IF($E$20<2,"Ball software",IF($E$20>=2,"Block software","The field is empty")

Thanks,
David


Posted by Eric on October 02, 2001 12:06 PM

There are several options...

If the numeric portion is always at the end, and always in the format "#.#", you could try

=right(E20,3)+0

on the other hand, if the numeric portion is at the end with a variable number of decimal places, you could try

=right(E20,len(e20)-find(".",E20)+2)

Alternatively, you could use the Data|Text-to-Columns and choose space as a custom delimiter to pull the numbers off of the end, then format that column as numbers.
HTH


the simplest thing for retrieving the number only would b

Posted by David Megnin on October 02, 2001 12:18 PM

Re: There are several options...

Eric,

Thanks for the push in the right direction.

Since there will always be a space between the text and the number I used this variation of your formula:
=right(E20,len(e20)-find(" ",E20))

thanks again!

the simplest thing for retrieving the number only would b : I have a form with a field for a software release number. The customer may put 1.1 or 6.5 etc. in the field or he may put Ball 1.1 or Block 6.x etc. in the field. : How do I perform a calculation using the number part only? For example IF($E$20<2,"Ball software",IF($E$20>=2,"Block software","The field is empty") : David