D-Concentrate numbers

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
Utf
4/5/2010 7:06:50 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
704 Views

Similar Articles

[PageSpeed] 1

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
David
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
Rick
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
Utf
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
Utf
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
Utf
4/5/2010 10:27:45 PM
Reply:

Similar Artilces:

number display
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 ...

Formatting Phone Numbers
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() ''...

Sorting pos. & neg. numbers in a cell
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...

Numbers to words
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...

counting the number of cell formats in a workbook
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:/...

Adding cells, using text as number
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...

Having numbers automatically appear bold
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...

count query and negative numbers question
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...

Page Numbering #11
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...

Numbers entry
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...

Number Format Problem Prevents Proper Sorting
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...

Why does my excel have numbered columns and rows
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...

D&B IDs into GP
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! ...

Adding record number to existing table
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...

Playing sound across only a certain number of slides
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?...

how do i change hours and minutes into a decimal number? #2
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...

Adding 1 to a number for 30 columns. #2
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 ...

Running Numbers
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...

Code number for Microsoft Office 2001
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...

Number Format #5
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...

How do I do a replace for a number of records?
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(...

Filter for 225 or 233 numbers #5
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...

Formatting numbers in Chinese
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...

sorting numbers in excel 2000
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...

assign numeric value to letters and sum with other numbers
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...