2

I have formula to extract numbers from Google XML response, that can be:

  1. 9 часов 24 минут
  2. 10 h 0 min
  3. 9 h 20 min
  4. 11 h 13 min
  5. 10 Std 55 min
  6. 9 timmar 5 min
  7. 19 min

Here is current formula (value is in BZ1):

=IFERROR(IF(VALUE(IF(FIND("min";BZ1;1)=11;MID(BZ1;FIND("min";BZ1;1)-2;1);MID(BZ1;FIND("min";BZ1;1)-3;2)))<30; VALUE(LEFT(BZ1;FIND("h";BZ1;1)-2))&","&5;VALUE(LEFT(BZ1;FIND("h";BZ1;1)-2))+1);"")

Formula rounds hours and minutes to hours, for example

  • 1 h 39 min -> 2
  • 10 h 12 min -> 10,5
  • 9 h 20 min -> 9,5

There is a problem that it is not able to take in consideration language changes for hours and min.

Is there any possibility to make it work so that it will:

  • If there is only number (case 19 min) -> extract number
  • If there are two numbers (case 1 h 39 min) -> extract first number as hours, then from last two spaces number as minutes

EDIT:

Check how many numbers in cell (target in CA25):

SUM(LEN(CA25)-LEN(SUBSTITUTE(CA25;{1;2;3;4;5;6;7;8;9};)))>1

If more than 1

LEFT(CA25;(FIND(" ";CA25;1)-1))&TRIM(MID(SUBSTITUTE(CA25;" ";REPT(" ";50));100;50))

If less than 1

LEFT(CA25;SUM(LEN(CA25)-LEN(SUBSTITUTE(CA25;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};""))))

All together

=IF(SUM(LEN(CA25)-LEN(SUBSTITUTE(CA25;{1;2;3;4;5;6;7;8;9};)))>1;LEFT(CA25;(FIND(" ";CA25;1)-1))&TRIM(MID(SUBSTITUTE(CA25;" ";REPT(" ";50));100;50));LEFT(CA25;SUM(LEN(CA25)-LEN(SUBSTITUTE(CA25;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};"")))))

This gives as output:

enter image description here

Now these need to be converted to hours and rounded up to one hour

EDIT 2:

Here is formula (target BZ1):

=IFERROR(IF(LEN(BZ1)-LEN(SUBSTITUTE(BZ1;" ";""))>2;LEFT(BZ1;(FIND(" ";BZ1;1)-1))+IF(TRIM(MID(SUBSTITUTE(BZ1;" ";REPT(" ";50));100;50))<60;1;1);IF(LEFT(BZ1;SUM(LEN(BZ1)-LEN(SUBSTITUTE(BZ1;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};""))))<60;1;1));"")
Improve this question
2
  • 1
    Any interest in a VBA solution ?? Commented Mar 3, 2020 at 12:31
  • Yes, after investigating this issue at the moment I am starting to think it might be the only one? Commented Mar 3, 2020 at 12:34

2 Answers 2

Reset to default
3

Here is a small user defined function:

Option Explicit
Public Function Tyme(inpt As String) As Double
    Dim arr, U As Long
    Tyme = 0
    arr = Split(inpt, " ")
    U = UBound(arr)
    If U = 0 Then Exit Function
    If U = 1 Then
        Tyme = CDbl(arr(0))
    Else
        Tyme = CDbl(arr(0)) + CDbl(arr(2)) / 60#
    End If
End Function

It:

  • is language-independent
  • returns an un-rounded floating point value (hours)

Some examples:

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=Tyme(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

NOTES:

  1. rounding should be applied outside the udf
  2. it would be easy to modify the udf to handle seconds as well
  3. it would be easy to modify the udf to return true Excel time rather than floating point hours
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

May I suggest you use a VBA function? Seeing as in your examples there is always a space between the numbers and other characters, this should do it:

Public Function TimeInHoursRoundedUp(rng as Range)
    Dim var As Variant: var = Split(rng, " ")
    Dim item As Variant
    Dim hour As Integer: hour = 0
    For Each item In var
        If IsNumeric(item) Then
            If hour = 0 Then hour = hour + item Else hour = hour + 1
        End If
    Next item
    TimeInHoursRoundedUp = hour
End Sub

Then in your excel sheet you could simply write =TimeInHoursRoundUp() and input the cell reference inside the brackets.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.