Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
pdgood
 
Posts: n/a
Default Removing an inch mark


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!C 37,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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Cutter
 
Posts: n/a
Default Removing an inch mark


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


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=539976

  #3   Report Post  
Posted to microsoft.public.excel.misc
Tim M
 
Posts: n/a
Default Removing an inch mark

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!C 37,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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert decimal to inch & fraction davepatrob Excel Discussion (Misc queries) 1 December 31st 05 06:05 AM
How do I insert a dot/check mark in a cell in Excel? sue New Users to Excel 3 October 27th 05 08:08 PM
How do I find and replace a question mark in Excel? Ranpalandil Excel Discussion (Misc queries) 1 September 7th 05 11:20 PM
Can tick mark labels be flipped about axis? [email protected] Charts and Charting in Excel 1 July 20th 05 06:03 PM
HOW DO I USE WATER MARK " DRAFT" IN EXEL john p max New Users to Excel 3 May 5th 05 03:21 AM


All times are GMT +1. The time now is 07:43 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"