How to split/grab number from string?

[QuantEdge]

hey, how can I grab the middle number in this number string 01:30.5 (A1)

cell should = 30 (B1)

with the small and easy Excel-Formula possible?

[oeldere]

=MOD($A1,SEARCH(":",$A1,1)+1,2)

but probably the question will be more complex.

[QuantEdge]

thx oeldere.. but it does work! please give example sheet.

it states "You've entered too many arguments for this function."

[AliGW]

It should be this:

=MID($A1,SEARCH(":",$A1,1)+1,2)

[QuantEdge]

still does work! please share sheet

my sheet attached

[AliGW]

Try this:

=SECOND(A1)

Make sure that B1 is set to general formatting.

Please stop asking people to share their sheets - it is you who should be doing that, as it is your data that is causing the issues.

[QuantEdge]

AliGW, sorry about that but still does not work please check out my attached sheet!

[CK76]

Try this then.

=INT(A1*24*60*60)

Oh wait, this works with your example but not when there's min/hour portion.

Give me a sec.

Edit: this ought to do it.
=MOD(INT(A1*24*60*60),60)

[oeldere]

=MID($A1,SEARCH(":",$A1,1)+1,2)

yes translation error

QuantEdge

thx oeldere.. but it does work!

That is strange, since in your added file it does NOT work.

But if it work, then the question is solved?

[QuantEdge]

thx oeldere, that works nicely

now is there any way to grab the first number on the left?

[MrShorty]

I might suggest that the OP is a little misleading. The OP refers to the cell as a "number string" when the cell actually contains a time serial number (review this to see how Excel stores date/time values: http://www.cpearson.com/Excel/datetime.htm ).

Here's how I would probably do it:

1) Extract the minutes value first. This is easily accomplished using the MINUTE() function https://support.office.com/en-us/art...d-a84801a60589 =MINUTE(A2)
2) AliGW's SECOND() function would work for the seconds value, if Microsoft's programmers had programmed the function to handle fractional seconds. However, it seems to be programmed to return only integer seconds, so we need to do it differently. Recognizing that the number in column A is just a fraction of a day, multiply by 24*3600 to convert to total seconds, then subtract off the number of seconds taken up by the minute's number. =A2*24*3600-B2*60 (assuming you put =MINUTES(A2) in B2).

[oeldere]

=left(a1,2)

[QuantEdge]

thx guy's got it working now... I will * both of you... you save me a day of clicking...

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.