Importing Measurement Fractions As Numbers Not Dates

Nov 25, 2009

I am creating a spreadsheet for use in a manufacturing environment to create a costing and cut list spreadsheet for production set up. The intent is that anyone can use this by copying and pasting an Excel Bill Of Material (BOM) that is generated out of a CAD program onto sheet 1. On sheet 2 (which they don’t touch) is the costing and cut list spreadsheet that references sheet 1 and automatically generated the numbers to create cut sizes and costs.

The first challenge I had was that the measurements include the inch symbol. For example if the imported text 15 5/8" is in cell A1 I am able to remove the inch symbol by using the following formula on sheet 2: =--LEFT(Sheet1!A1,LEN(Sheet1!A1)-1). The result is: 15.6250 which is exactly what I want.

Here is the problem I am having. When the listed dimension is less than 1” and is 5/8” for example I get ###### because I have the cell formatted as a 4 decimal place number. If I change the formatting to number with zero decimal places it becomes 39941 which is how many days it has been from January 1, 1900 to May 8, 2009.

The question is what formula should I use to change the 5/8” imported text so it says 0.6250 and can be used in math formulas? Please keep in mind that the imported text could be a measurement with a small number like 1/16” to a large one such as 284 3/4" and that the imported text “is what it is” and cannot be manually changed or adjusted (because that presents the risk of people creating errors and costing the company money by ordering the wrong material sizes).

View 10 Replies


ADVERTISEMENT

Fractions To Numbers?

Aug 7, 2009

I have some numbers that I've imported from the internet. Some of them contain fractions (i.e. 7 1/2) where the 1/2 is a single fraction character. Is there a function I can use to automatically turn these fraction characters into numbers when they're imported to excel via web query?

View 10 Replies View Related

Organize Numerous Numbers By Fractions

Jun 19, 2007

I am currently trying to organize numerous numbers by fractions, and it just will not set up right.

1 1/2
1/2
3/4
2
1
2 X 3/4
1 X 1/2
1 1/2 X 3/4

What would be the best code used, or method, to ensure that these numbers organize correctly?

View 2 Replies View Related

Measurement Conversion Format Cell

May 30, 2007

What is the custom format cell type I need to create to make cells in inches (such as 0.125, .25, .375, 3.125) into the format of 00-00-0/0 where the 0/0 is 1/8 of an inch? So, 4.25 would become 00-04-2/8. 62.25 inches would become 05-02-2/8.

View 9 Replies View Related

Importing Phone Numbers And Contacts

May 15, 2007

How do i go about importing contacts - names, numbers, addresses that are in excel into Microsoft Outlook?

View 2 Replies View Related

Convert Numbers, Representing Dates To Actual Dates

Oct 15, 2009

I get a spreadsheet every day that has a column of numbers that contains dates that are not in a date format. Is there a way to convert these numbers to a date format? The numbers come from an outside source so I can't modify on the front end. look at the attached spreadsheet with representative data of what I'm trying to ccomplish.

View 2 Replies View Related

Convert Numbers Representing Dates To True Dates

Jun 29, 2008

I import data from my works server and dates are represented in text. example text format is yyyymmdd/19790310. What I'm trying to do is add and subtract from this date. I was trying to utilize formula and havent got close. I have a couple of parameters:

In column L I would like to represent the following

if column "B" is "A" add 38 years to column "J" dates are represented as TEXT
if column "B" is "B" add 35 years to column "J" "
if column "B" is "C" add 30 years to column "J" "
if column "B" is "d" add 30 years to column "J" "

Column "M" I would like to find the difference in column "L" and "C" I know days will be difficult it can be represented in years and months only.

I have no preference on whether I use VBA or a formula but have failed on on formula and was attempting to use VBA Case method but no success. I have 10000 rows of information and am open to any ideas attached an example of my data.

View 7 Replies View Related

Concatenating Fractions

Apr 6, 2008

I'm trying to create a model for woodworking where I can enter fractional wood sizes and have Excel create panel sizes and board lengths for cabinetry. I can easily get the inputs formatted in fractions, but then I'd like to have a concatenation formula that joins two fractions and displays a panel size as a fraction. However, when I try to do this, the concatenation formula changes the fractions to decimals.

Example: cell A1 is 3/4" formatted as fraction. Cell A2 is 1 1/2" formatted as fraction. Let's say I want a to concatenate so that A3 reads

3/4 x 1 1/2

But when I use concatenate (A1," x ",A2) the answer in A3 reads

.75 x 1.5

View 9 Replies View Related

Specific Fractions In Cells?

Feb 11, 2014

How do you limit a cell to only display fractions in sixteenths, eighths, quarters, and halves?

I'll give an example.....we use excel to cut list doors in our cabinet shop. On a three panel door it will spit out a fraction of 1/12" (or 5/12, or 7/12 etc), but this looks a lot like 1/2" and the guys frequently cut them the wrong size.

Is there any way to limit a cell so it will only display in these fractions 1/16, 1/8, 1/4, 1/2 etc? and I don't mean by just saying to format in sixteenths, because if I do that then it displays 12/16" when I want it to say 3/4".

I basically want it to round to the nearest sixteenth, but I want it to display to the closest sixteenth, eighth, quarter, half etc.

View 3 Replies View Related

Formatting Cells As Fractions

Nov 8, 2008

I have a spreadsheet to calculate piping offsets, center to center.
The formula in B:1 is simple. Take the length in cell A:1 and multiply
it by the square root of 2.

I then increment the length by 1/8 of an inch through cell A:180 and those
results are place in B:2 thru B:180.

The results are display to the nearest 1/16 of an inch. But if the actual
dimension is eight and one half inch, it is displayed as 8 8/16. For aesthetic
reason i wish to display the reducible fractions reduced. So that 8 8/16
is displayed as 8 1/2 inch.

I understand that i may go to each cell and individually format it accordingly
as halves
as quarters
as eights
but was wondering if there is an easier method.

View 6 Replies View Related

Convert 1/2-20 3a Fractions Into Decimal

Jun 24, 2009

I need to convert the fraction at the beginning of a cells text into the decimal equivelent. for example 1/4-20 3A would give .2500. I have this formula so far that works perfect for fractions that only contain 1 number on each side of the division sign such as 1/2.

View 2 Replies View Related

Text And Fractions In Same Cell

Jun 28, 2013

I have this in a cell Plam Base 36"w x 28.5"h x 22.5"d I want this Plam Base 36"w x 28 1/2"h x 22 1/2"d

How do I do it.

View 8 Replies View Related

Fractions To Decimal Conversion

Dec 14, 2009

I have received help on this topic in the past and I though I had solved the issue, however I realized recently that my formula will not work on any fractions larger than 1 inch. I am converting machine threads in fraction form to a decimal equivalent. here is an example of the what the entry looks like before it is converted.

ex,
1/2-20 3A (becomes .5000)or
3/4-13 2A (becomes .7500)or
1-14 3A this one will not work with my current formula (should be 1.0000);

View 12 Replies View Related

Fractions To Decimals With And Without Hyphens

Feb 12, 2014

I have fractions and whole numbers in a column as per the following.

E13 = 3/4
E15 = 5
E17 = 1-1/2

I can use the formula =SUBSTITUTE(E17,"-"," ")+0 which gives the correct result of 2.5 and also correct
for E15 with the answer 5.

But I'm stumped how to get it correct for E13 where it is less than whole number.

I can use the formula =IF(E13=0,0,("0 "&E11)+0) but that won't work for the remainder of the cells.

View 1 Replies View Related

How Can I Convert Decimals To Fractions?

Nov 19, 2008

1.25 is in cell A1. This number will often be buried in a longer part number that may look like ELL-1.25-S40.

I would like to use a formula in cell A2 to display the fractional equivalent (1-1/4).

View 9 Replies View Related

Converting Fractions To Decimals

Aug 11, 2009

=("0 "&C3)+0

have this formula for converting fractions to decimals which works great,

the problem i have is to get it to register 0 if there is no fraction in c3

will try and word my next question better

View 9 Replies View Related

Decimal To Inch Fractions

Sep 23, 2003

Need to convert decimal to inch fractions IE: 4.5 = 4 1/2 inches

View 9 Replies View Related

Fractions In UserForm TextBox

Nov 11, 2006

I know I can use fractions in Excel, eg. 1/4 will yield .25 when formatted for number.

I wanted to allow users to enter fractions in a userform, but alas, it seems to come out as text. So I gave up on the idea, and then it hit me that this forum might be able to provide the answer. If so, maybe I'll include it before I finish my project.
In a nutshell, I want to have users put in fractions, decimals or whole numbers in the userform so I can use it in formulas. (I haven't given any thought to handling combinations like 1 1/2. Later for that). Is there any way for the Excel to know that the textbox on the userform is a number?

View 3 Replies View Related

Formula Returns Value With Fractions Of Pennies Even Though There Are None

Feb 14, 2014

I created a table for a check register. Column D is the transaction amount and column I is the updated balance. Column D has no formulas. It is where I enter the transaction amount. I checked all entries and even re-typed them. I extended the decimals out to the maximum and they are all. $$.CC000000000etc - no fractions of pennies. However the Subtotal 109 formula at the bottom of the table computes the answer with fractions of pennies even though there are no entries with fractions of pennies. And at row 118 in column I it also starts computing it as fractions of a penny.

Column I has a formula. Row 1 of course is titles. The second row is my begining balance with this formula: +"Table Name"[[#This Row],[Amount]] In other words what is in Column D row 2 as my first transaction, the beginning balance. The rest of the column has this formula +I(row above)+$D(that row) so in column 3 it is +I2+$D3 and so forth down the rest of the table. It had read +I2+"Table Name"[[#This Row],[Amount]] but I changed it to $D because I thought that might fix the error. It didn't

Columns D and I are the only ones with dollar amounts and really any numbers, except for the dates in column B. E is the envelope, F is the category, and G is the transaction status (cleared, pending, etc.). They have drop down boxes. Column H is notes. It is formatted as text. In the transaction amount column (D) I enter debits as negative numbers and credits as positive. e.g., a check written for $20 is -20.00 and a deposit of $20 is 20.00. Column I, my balance does not have any negative numbers. -fortunately-

The problem is minor and I could work around it. However it impacts my formula which tells me that I "balance." It will not return the value "Balanced" as it is computing a variance between my bank balance+uncleared items being equal to my CR balance by a fraction of a penny.

View 3 Replies View Related

Unique Fractions To Decimal Formula

Jun 1, 2009

I need to find a way to convert the fraction located at the beginning of a text string to decimals. Here is an example of what the cell containing the fraction will look like; 1/4-20 3A. I need to convert the fraction and save/copy paste the decimal answer to another cell.

View 3 Replies View Related

CONCATENATE Cells Formated As Fractions

Apr 26, 2006

I want to concatenate several cells, some of which are formated as fractions,
and have it display those numbers as a fraction. When I perform the
function, it displays the cell in its decimal form.

Example:
=CONCATENATE(E2," ( ",F2," in. "," ",G2," ",H2," in. ", I2," ",J2," in."," )
"," color #")

Where: F2, H2 and J2 are fractions.

Result: 12" Stretcher ( 11.625 in. x 23.625 in. x 3.625 in. ) color #

View 12 Replies View Related

Time Fractions And User Input

Mar 12, 2008

What I am doing is creating a spreadsheet solution to tack race times. These show up in past performances in the following formats in paper copies: 45 2/5 seconds. The end user would have to put in maybe 30 of these times based on the number of contestants. Keeping in mind that the 45 2/5 number will be used in some calculations, I want to give the user the easiest input interface. Meaning instead of inputting like this; 0:45.4 (mm:ss/00 format), I would like them to just enter 45.4. the spreadsheet would input format this cell into a time format that would be able to have calculations done to it. Another example might be 1:35.60 (mm:ss.00 format) which is 1 minute 35 and 3/5 seconds. This would be entered as 135.6.

A few questions first:

1) Would it be easier to leave the number inputed alone until the calculations ar finished then translate into a time factor?

or

2) would it be necessary to convert into a time factor as inputted be cause of the 60 factor involved with the time factor?

Having asked those questions, setting this all up with the exact scope I am working towards, any help or sugesstions as to the best way to proceed would be fantastic!

View 9 Replies View Related

Format Cells As Fractions Of Seconds

Mar 30, 2007

I am trying to set up a workbook to record times for our swimming club. This will involve recording the swimmer's time as minutes, seconds, tenths and hundredths. I would then want to be able to compare the new time to the swimmer's previous personal best and calculate the difference.

View 3 Replies View Related

Convert Fractions To Decimals And Decimal To Fraction

Feb 6, 2009

I know that if I enter =3/4 in a cell it will give me .75 but is there is formula to convert fractions into decimals or is this the best way to do it? I was also wondering if there is some way to convert the decimal amount to a fraction

for measurement conversions I know that
=B3*25.4 will convert a decimal to mm and so will the formula =CONVERT(E4,"in","mm")

and to convert mm to a decimal a few formula that work are
=CONVERT(H3,"mm","in")
=MROUND(H4/25.4, 1/8)
=H5/25.4
=INT(H6/25.39999918*8)/8

But is there a way to convert a fraction to the decimal and is there a way to convert mm to a fraction instead of the decimal equivalent of the fraction?
or maybe a formula to convert mm to inches but instead of the result being in decimal format it would be in fraction format?

View 4 Replies View Related

Trig Function That Display Radicals And Fractions?

Jan 1, 2012

If I do =sin(radians(30)) I get .5 and =sin(radians(45)) I get .707106

These are of course correct, but I would like to get 1/2 and sq root 2/2 (with the actual radical sign).

View 9 Replies View Related

Dates Appearing As Numbers

Dec 7, 2009

i had a situation where i import data from web.The sheet contains date & other columns.Whenever,i copy the data and paste it in my worksheet Using Paste Special >> Values i get some numbers instead of Dates.How would i correct the numbers to date format with out loosing the original date format(DD-MM-YYYY). Also the dates are aligned irregularly when i paste them to column.

View 3 Replies View Related

Turns Numbers Into Dates

Aug 1, 2013

When I write out ranges in cells like:

1-10
10-20
20-30
30-40
Etc.

Excel turn some of these ranges into dates such as 01-Oct, which is extremely annoying. But trying to change to different date formats but it still keep it to dates. what I can do?

View 5 Replies View Related

VBA Read Dates As Numbers?

Sep 23, 2009

I have a userform that has several textboxes and checkboxes. Some of the textboxes read and display dates from certain cells. I have the checkboxes conditionally formatted based on the data in the textboxes. However it doesn't seem to be reading the dates as a value. Here's an example of my issue to make it clear for you:

txtBox 1 = 01Sep2009
txtBox 2 = 01Oct2009

What I need is for the checkbox text colour to change from black to red if txtBox1 < txtBox2. Here's the code I have in place:

View 4 Replies View Related

Adding Numbers Using Dates

May 10, 2007

I have a spread sheet that shows the dates items were shipped. When trying to work out the value of parts shipped in a month using SUMIF I get the value zero, I believe this to be because (obviously), there are multiple dates of ships during the month, I therefore tried ignoring the day of ship in the SUMIF but this did not work, the date format is DD/MM/YYYY.

View 9 Replies View Related

Calculating Dates And Numbers

Apr 3, 2007

I am starting a project that will involve finding how old people are using the difference of two years. Cell A1 will contain the function that automatically obtains the current year from the PC clock (or a manual input). Cells B1...B6 will contain text (names). Cells C1...C6 will contain the month of their birth. Cell D1...D6 will contain the day of their birth. And cells E1...E6 will contain the year of their birth. Cells F1...F6 will have the formula to calculate the difference between A1 and its respective cell column E.

The only functions I know are =(TODAY) which does not give me just the year. Also, I do not know how I would be able to do the calculation [e.i., =(TODAY) - 1988].

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved