Removing an inch mark

[pdgood]

Hi,
In cells D4:D13 I ask the users of this Excel sheet to type in a thickness of material. This is a decimal followed by three digits. Example: .250
But if they type in the inch mark after the thickness, it messes up the formula I have in related cells E4:E13.
For example: In E4 I have
=(IF(D4<=0.225,Pricing!C2,IF((D4>=0.301),Pricing!C37,Pricing!C19)))
All is well if the user types in something like .250, but if they type in .250" my formula does not work. I can't use drop down menus or Lookup lists because there are an almost infinite number of thicknesses.
Is there anything I can add to my formula to make it remove the inch mark if one is present? Is there anther solution?
Many thanks in advance.

[Cutter]

How about Data > Validation
Select the cell that is used for the thickness input
Go to Data > Validation
On the Settings tab under "Allow" choose decimal
Set your Maximum and Minimum values which would cover acceptable entries
Then click the Input Message tab and type a message which will show up when the user selects that cell - something like "Thickness Required - Type numeric characters only"
Then on the Warning tab type in another message which will show up if your user fails to follow the first message - something like "Invalid Entry - Do not type non-numeric characters"
Then click OK

[Tim M]

You could use data validation for this perhaps. Under 'data'....'validation'
you can allow for a decimal number. It will ask you to enter a range (just
choose the min and max possible numbers they could enter) Then in the error
notification field type something like 'Invalid entry, do not use " inches
symbol.' since putting the " at the end makes the data alphanumberic rather
than just decimal it will not allow them to enter the number with the "

"pdgood" wrote:

>
> Hi,
> In cells D4:D13 I ask the users of this Excel sheet to type in a
> thickness of material. This is a decimal followed by three digits.
> Example: .250
> But if they type in the inch mark after the thickness, it messes up the
> formula I have in related cells E4:E13.
> For example: In E4 I have
> =(IF(D4<=0.225,Pricing!C2,IF((D4>=0.301),Pricing!C37,Pricing!C19)))
> All is well if the user types in something like .250, but if they type
> in .250" my formula does not work. I can't use drop down menus or
> Lookup lists because there are an almost infinite number of
> thicknesses.
> Is there anything I can add to my formula to make it remove the inch
> mark if one is present? Is there anther solution?
> Many thanks in advance.
>
>
> --
> pdgood
> ------------------------------------------------------------------------
> pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
> View this thread: http://www.excelforum.com/showthread...hreadid=539976
>
>