Prev: Lookup and count in the same formula
Next: Excel cells changes number format automatically
From: Dale G on 18 May 2010 10:40
Hi,
In cell A2, need to take this concatenated text and time string
510n0.240972222222222 and separate it in to cell B2 & C2. The left is the
text which I can get with =LEFT(A2,4) for my desired result of 510n. The
right I need to have in a time format like 5:47 that's where I'm stuck.

Any help is appreciated

From: Pete_UK on 18 May 2010 10:45
Try this:

=--RIGHT(A2,LEN(A2)-4)

and format the cell as a time in the format you require.

Hope this helps.

Pete

On May 18, 3:40 pm, Dale G <Da...(a)discussions.microsoft.com> wrote:
> Hi,
> In cell A2, need to take this concatenated text and time string
> 510n0.240972222222222 and separate it in to cell B2 & C2. The left is the
> text which I can get with =LEFT(A2,4) for my desired result  of 510n. The
> right I need to have in a time format like 5:47 that's where I'm stuck.
>
> Any help is appreciated

From: Dave Peterson on 18 May 2010 10:49
After you split the value into two cells, try formatting the second column as a
time (like: hh:mm:ss)



On 05/18/2010 09:40, Dale G wrote:
> Hi,
> In cell A2, need to take this concatenated text and time string
> 510n0.240972222222222 and separate it in to cell B2& C2. The left is the
> text which I can get with =LEFT(A2,4) for my desired result of 510n. The
> right I need to have in a time format like 5:47 that's where I'm stuck.
>
> Any help is appreciated
>
From: Mike H on 18 May 2010 11:26
Hi,

You could do this and format the cell as time

=MID(A1,FIND(".",A1),LEN(A1))+0

or this

=TEXT(MID(A1,FIND(".",A1),LEN(A1)),"hh:mm")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Dale G" wrote:

> Hi,
> In cell A2, need to take this concatenated text and time string
> 510n0.240972222222222 and separate it in to cell B2 & C2. The left is the
> text which I can get with =LEFT(A2,4) for my desired result of 510n. The
> right I need to have in a time format like 5:47 that's where I'm stuck.
>
> Any help is appreciated
>
From: Dale G on 18 May 2010 11:59
Thanks everyone for all your help.

I went with this =TEXT(MID(A1,FIND(".",A1),LEN(A1)),"h:mm")

big time saver, thanks again.

"Mike H" wrote:

> Hi,
>
> You could do this and format the cell as time
>
> =MID(A1,FIND(".",A1),LEN(A1))+0
>
> or this
>
> =TEXT(MID(A1,FIND(".",A1),LEN(A1)),"hh:mm")
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Dale G" wrote:
>
> > Hi,
> > In cell A2, need to take this concatenated text and time string
> > 510n0.240972222222222 and separate it in to cell B2 & C2. The left is the
> > text which I can get with =LEFT(A2,4) for my desired result of 510n. The
> > right I need to have in a time format like 5:47 that's where I'm stuck.
> >
> > Any help is appreciated
> >
 | 
Pages: 1
Prev: Lookup and count in the same formula
Next: Excel cells changes number format automatically