I am trying to delete some numbers from a column for example: column A has total duration of a task times 0587-1856 and I would like to have column B with only first four numbers 05:87 (start time for the task). Please help on this formula. Thanks in advance for your help. Kimti

0 |

4/5/2010 7:06:50 PM

You might need to explain what sort of time 05:87 is. For most of us, there are only 60 minutes in an hour, and only 60 seconds in a minute. If you have text such as 0527-1856 in column A, and you want to get a time of 05:27, try =--TEXT(LEFT(A1,4),"00\:00") and format as time to suit. -- David Biddulph "Kimti" <Kimti@discussions.microsoft.com> wrote in message news:6BDB9C41-C30C-46A5-BB51-6F2AA21D2CE9@microsoft.com... > I am trying to delete some numbers from a column for example: > > column A has total duration of a task times 0587-1856 and I would like to > have column B with only first four numbers 05:87 (start time for the > task). > > Please help on this formula. > > Thanks in advance for your help. > > Kimti

0 |

4/5/2010 7:27:46 PM

What kind of time is 05:87... 87 minutes after the hour? -- Rick (MVP - Excel) "Kimti" <Kimti@discussions.microsoft.com> wrote in message news:6BDB9C41-C30C-46A5-BB51-6F2AA21D2CE9@microsoft.com... > I am trying to delete some numbers from a column for example: > > column A has total duration of a task times 0587-1856 and I would like to > have column B with only first four numbers 05:87 (start time for the > task). > > Please help on this formula. > > Thanks in advance for your help. > > Kimti

0 |

4/5/2010 7:30:01 PM

In B1: =LEFT(A1,2) & ":" & MID(A1,3,2) -- Gary''s Student - gsnu201001 "Kimti" wrote: > I am trying to delete some numbers from a column for example: > > column A has total duration of a task times 0587-1856 and I would like to > have column B with only first four numbers 05:87 (start time for the task). > > Please help on this formula. > > Thanks in advance for your help. > > Kimti

0 |

4/5/2010 8:00:58 PM

Try: =LEFT(A1,2)&":"&MID(A1,3,2) But..., the last tine I've checked - there were not more than 60 minutes in an hour. Micky "Kimti" wrote: > I am trying to delete some numbers from a column for example: > > column A has total duration of a task times 0587-1856 and I would like to > have column B with only first four numbers 05:87 (start time for the task). > > Please help on this formula. > > Thanks in advance for your help. > > Kimti

0 |

4/5/2010 8:07:32 PM

Thank you David, it worked OK. "David Biddulph" wrote: > You might need to explain what sort of time 05:87 is. For most of us, there > are only 60 minutes in an hour, and only 60 seconds in a minute. If you > have text such as 0527-1856 in column A, and you want to get a time of > 05:27, try =--TEXT(LEFT(A1,4),"00\:00") and format as time to suit. > -- > David Biddulph > > > "Kimti" <Kimti@discussions.microsoft.com> wrote in message > news:6BDB9C41-C30C-46A5-BB51-6F2AA21D2CE9@microsoft.com... > > I am trying to delete some numbers from a column for example: > > > > column A has total duration of a task times 0587-1856 and I would like to > > have column B with only first four numbers 05:87 (start time for the > > task). > > > > Please help on this formula. > > > > Thanks in advance for your help. > > > > Kimti > > > . >

0 |

4/5/2010 10:27:45 PM

Hello everyone, This may seem like a basic question, but I am self taught in excel and just can't figure this one out. I have a figure 4,500,000.00 and I want to display it in a report as 4.5 (4.5 million instead of showing all the zeroes) I have looked through all the formats and can't seem to find anything that will do this for me, does anyone know a way?? Thanks! Hi try the following custom format: #,,.0 -- Regards Frank Kabel Frankfurt, Germany cord wrote: > Hello everyone, > > This may seem like a basic question, but I am self taught in excel and > just can't ...

Consider this example: A1 -> 3365551111 A2 -> 336-555-2222 A3 -> (336) 555-3333 I want to be able to scan through column A and change all numbers to be in the format of A1 which is just straight numbers...no punctuation. How would I do that? -- Tha BeatMaker ------------------------------------------------------------------------ Tha BeatMaker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23998 View this thread: http://www.excelforum.com/showthread.php?threadid=383980 Easiest would be to use a macro. Sub RemoveAlphas() ''...

In a single cell I have both positive and negative numbers (representing an annual total). I'm trying to sort them by pos. and neg. I'm currently using a SUMIF(range, ">0", range) and another for <0, but that doesn't sort them properly. Example: I have a cell with the following: =123649+113634+279384+129487-12045 when I use SUMIF for positive, I get 877,599 but when I use it for neg. I get 0. Any ideas? Thanks. LZ, Your formula returns only one value to the cell -- the total. That's what the SUMIF is seeing. There is no Excel command that will change t...

Hi Is there a function in access which will convert a number into corresponding words e.g. 21 becomes twenty first, 24 becomes twenty fourth and so on? Thanks Regards Access doesn't provide such a function. You could create one. You might want to generalize it for several cases, the first two being "ByDigit", "SyntacticallyCorrect", "Integer", ..... Just as sure as you do it for one class of number and expression you'll find another need. HTH -- -Larry- -- "John" <John@nospam.infovis.co.uk> wrote in message news:e2Mei3PEIHA.10...

I'm currently facing the EXCEL error "number of different cell formats too high" (it is a raw translation of french text, so maybe it's not the same message in english) I know that the maximum number of different cell formats is about 4000 per workbook. So my point is : How to trace the evolution of the current number of different cell formats in my active workbook? Which property of which object will give me this figure? Thank you J�r�me -- jérome Yacc ------------------------------------------------------------------------ jérome Yacc's Profile: http:/...

HELP! I haven't worked in Excel much, but I know there's an equation I can use to get the information I'm wanting.... For example, I'm trying to keep tract of how many apples everyone eats. I'm going to assign the letter "a" to Amy, "b" to Betty, "c" to Cathy, etc. Amy eats an apple, so in cell A1, I'm going to type "a", and I want a "1" to go to cell B6. (=COUNTIF(A1,"a") Now here's where I'm getting myself confused....now Cathy's eaten an apple, so I want to type a "c", and I wa...

Hi all, Had such great advise last time, thought I'd try again. I have a large range of cells where I will be inputing sales figures i the form of currency revenue. The range is about G-I and 1-400. Th sales data will be a comparisson of YTD 2001 - 2004 revenue results. What I hope to do is have any figure that is above $30,000 appea automatically in bold so as to stand out as a good client. I gues there is something in the IF function to do this but I'm stumped. Any ideas? Thanks again in advance. Saxte -- Message posted from http://www.ExcelForum.com select the range you wa...

Hi All, Pretty new to database queries so apologise if I sound dumb! I have a database for stock control of energy efficient light bulbs for a charity that gives the bulbs out free to folks in need. The database has various fields including type of bulb, location, quantity and whether the stock has been added to stock or removed. I want to know how many of the different types of bulbs are in stock at any time. I have tried to run a query which makes the 'quantity' amount of bulbs removed to be negative (puts a '-' in front of number). I then want to run a second q...

In Publisher 2003, I created a 20 page booklet. The first six pages were created separately and then on pages 7-20 the text boxes are linked. I inserted page numbering and chose the option to not show page numbers on the first page. The problem is that the page numbers appear only on pages 7-20. I can't figure out why pages 2-6 don't show the page numbering. I'm assuming it has something to do with pages 7-20 having linked text boxes. Can you suggest how to get the page numbering to show on the pages 2-6? Thanks. Are you sure the page numbers aren't covered by ob...

In Excel 2003, under Options>Edit>Fixed Decimal Places is set at 2. If I enter 500. in a cell this appears as 5.00; it is necessary to enter either 500.0 or 50000 to get 500.00 In earlier versions of Excel it was only necessary to enter a number followed by decimal point to get the whole number in the cell. Is there a way to make Excel 2003 behave the same as the earlier versions? Hi goto 'Tools - Options - Edit' and uncheck 'Fixed decimals' >-----Original Message----- >In Excel 2003, under Options>Edit>Fixed Decimal Places is set at 2. If I >ent...

I have a long list of numbers that were exported from anothe application that I can't seem to format properly to sort in ascendin order. I have changed the format from General to Number and back wit no success. I'm sure that this is an easy fix, but I'm not seeing th answer. The third entry below (1103) should be toward the top of th list. Any ideas? 110272 110277 1103 110329 110338 11033 -- 65Stan ----------------------------------------------------------------------- 65Stang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1500 View this threa...

I use Microsoft Excel 2002 on Windows XP. The rows on the spreadsheet are numbered and the columns (that are normally alphabet) are numbered also. Why don't I have the traditional ABC columns and 123 rows? How can I fix this? When I installed the software, it had ABC and 123; however, once I began using the program, it became numbered. Thank you. Tools>Options>General. Uncheck "R1C1 reference style". Gord Dibben Excel MVP On Fri, 21 Jan 2005 16:05:08 -0800, "DLYNN" <DLYNN@discussions.microsoft.com> wrote: >I use Microsoft Excel 2002 on Wind...

We want to store Dunn & Bradstreet ID's for our customers in the Great Plains database. What table and field do you store this ID in Great Plains? RM00101? Which column have you used for this purpose? Thank you! ...

I'm trying to write an update query to add a record number to a group of records. The table looks like this: ID Name Date Day 1 Jane 1/1/07 1 Jane 1/2/07 2 John 1/5/07 I'm trying to update the "Day" field where the count starts over for each new ID number at 1 and then runs sequentially. Can anyone help? Substitute your table and field names -- SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1 WHERE Q1.[Group] = Q.[Group] AND Q1.Points < Q.Points)+1 AS Rank FROM Product AS Q ORDER BY Q.Group, Q.Points; If you h...

Hi, I use powerpoint 2007, and am trying to embed a song in a presentation. I want the song to play across several slides, but then stop when I move to the next slide rather than simply play until the song ends. When I go to the options-tab and select 'play across slides' from the 'Play sound' drop-down menu, the song then plays until it ends, rather than stop after 4 slides like I want it to. Powerpoint 2003 had an option for the number of slides for which the song would play, but I can't find this in the 2007-version. Is there really no way to do this?...

i am trying to turn total flight hrs into a decimal number( ie: 2045hrs 10mins into 2045.10). can anyone help me with a formula which may help in this? time is given HH:MM but i need it turned to HH.M. Hi, The decimal time for 2045h 10m is obtained by multiplying the time by 24 and formatting as general or a number but this will give an answer of 2045.167 and not 2045.1 because .1 of and hour is 6 minutes not 10. Mike "Mills00" wrote: > i am trying to turn total flight hrs into a decimal number( ie: 2045hrs > 10mins into 2045.10). can anyone help me with a formula w...

Thank you all!, but garfield-n-odie, hit the nail on the head. This is a wonderful place for people to come with questions for exce and all of you are the reason why. Keep up the great work and thank yo all once again. Jason -- Jason26 ----------------------------------------------------------------------- Jason263's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1479 View this thread: http://www.excelforum.com/showthread.php?threadid=26635 ...

Hi all, I am a novice wrt Excel. I had created a simple Invoice, to save as a template. Would like to have the Invoice Number cell automatically in running number, for each invoice I created. Example my first invoice will have the number 001. Once finished and saved, opening up another new invoice, using the template, it would now show 002, and so on. Truly appreciate some advise to do this. Thanks in advance. AlFlo Hi AlFlo, See JE McGimpseyy's Sequential Numbers page at: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html If you are not familiar with macros, check the Qu...

My iMac crashed about two months ago. In the process of rebuilding it, I removed Microsoft Office 2001 to free space for the rebuild process. Unfortunately I have lost the sleeve with the product code on it. What can I do? Please reply to wnlewis@southwind.net Wm. Neal Lewis In article <d61c01c3f00a$2f7c7290$a101280a@phx.gbl>, "Wm. Neal Lewis" <anonymous@discussions.microsoft.com> wrote: > My iMac crashed about two months ago. In the process of > rebuilding it, I removed Microsoft Office 2001 to free > space for the rebuild process. Unfortunately I have lost...

Hello All, Using Excel XP. In column A I will be entering data that has a format of the first two numbers are years (eg: 06) followed by a dash, then a number from 1 to 9999, another dash and then ending with the letters PR. The data will look like this: A1: 06-45-PR A2: 06-123-PR A3: 04-68-PR A4: 06-6-PR After putting the data in column A I want to sort them by ascending order by #, so that in the above example I want to get: 04-68-PR 06-6-PR 06-45-PR 06-123-PR . But .it sorts by the first two digits and then the 1st digit after the first dash: 04-68-PR 06-123-PR 06-45-P...

I am importing a file which will contain about 20 records (20 fields per row) into an access database table. I want to be able to automate doing a REPLACE across all the records I have imported. (because. in order to make the import work, in the Excel csv file I replaced all commas with the word 'comma'. I now want to replace the word 'comma' with actual ','). Do I make sense? any help very gratefully received. thanks Nicola Access 2007 Continue this SQL string for all 20 fields using your table and field names. UPDATE 1st_Table SET [1st_Table].Field1 = Replace(...

that works again but I'm looking for filter that will get rid of all o the ones I dont want. I can easily enough find a 225 or 233 no by usin the auto filter but I need a formula to get shut of the ones i don want ie 3052 01517334519 07969760557 01512333016 TRUE- here 1 only want 233 or 225 no's to show up lik this one 3018 3018 22528 TRUE--- here 2 --- dont want this to show u :mad: 02072330082 NB so long as it always counts from the right four digits and the 23 or 225 no is no. 987654321 no. of digits from RIGHT 01512253003 digits no. 5 6 7 is the 22...

Here's a question that arose: I was talking with someone about the numeric formatting problem, and he reminded me about a discussion we'd apparently had over a decade ago, about Chinese numbers. There are 10 symbols for the digits 0,1,2,3,4,5,6,7,8,9 but then ten is represented by something, that as best I recall, looks like a +, so (forgive the Arabic numerals here, but I don't know Chinese anyway...) 11 would be represented by the symbol +1, twelve by +2, through nineteen +9, then 20 would, if I'm remembering correctly, be the characters for 20, up to 100, which would use th...

I have a list of a little over 12,000 numbers i want to sort in ascending order (ie. lowest to highest number). i use the sort function and the list returned isn't in ascending order. for instance my list sorts the numbers from 10,000 to 28,000 and then starts at 5,000 and goes to 9,999. and throughout the list, numbers such as 12518, 125181, 125183, and 12519 will follow each other. i'd like the 12518 to be followed by 12519 and 125181 followed by 125183 so numbers are in ascending order. I've read that excel sorts numbers in certain order, how do i circumvent the d...

I apologize if I am duplicating an earlier question, but I can't find the answer. How do I sum a row or column that has numbers and letters by giving the letters a numerical equivalent? -- WJG On Mon, 11 Jan 2010 12:19:01 -0800, Galadad <Galadad@discussions.microsoft.com> wrote: >I apologize if I am duplicating an earlier question, but I can't find the >answer. How do I sum a row or column that has numbers and letters by giving >the letters a numerical equivalent? Could you give an example of input and expected output. Lars-�ke Just guessing...