Language translation formula (Excel)

Excel formula that can translate From any language To any language.
Example: getGoogleTranslation("Do you speak English?";"en";"sv")

However this code has one limitation; it can only translate one sentence.


--
Public Function getGoogleTranslation(strSource As String, strSourceLang As String, strDestLang As String) As String
    Dim strURL As String, x As String

    strURL = "http://translate.google.com/translate_a/t?client=t&text=" & _
             Replace(strSource, " ", "%20") & _
             "&hl=en&sl=" & strSourceLang & _
             "&tl=" & strDestLang & "&multires=1&pc=0&rom=1&sc=1"

    With CreateObject("msxml2.xmlhttp")
        .Open "get", strURL, False
        .send
        x = .responseText
    End With

    getGoogleTranslation = Replace(Replace(Split(x, Chr(34) & "," & Chr(34))(0), "[", ""), """", "")

End Function
--

Source: link

Extract hyperlink formula

Example:
=HLINK(A1:A10;FALSE)

Function HLink(rng As Range, Optional RtnSub As Boolean) As String
 If rng(1).Hyperlinks.Count Then
    If RtnSub = False Then
       HLink = rng.Hyperlinks(1).Address
        Else
       HLink = rng.Hyperlinks(1).SubAddress
    End If
 End If
End Function

Source: link, subaddress property

Concatenate a Range with Separator (optional)

The following custom function concatentates the data in a range...

ALT. 1: Source: link

....................

Function AConcat(a As Variant, Optional Sep As String = "") As String

    'By Harlan Grove, March 2002

    Dim Y As Variant

    If TypeOf a Is Range Then

        For Each Y In a.Cells

            AConcat = AConcat & Y.Value & Sep

        Next Y

    ElseIf IsArray(a) Then

        For Each Y In a

            AConcat = AConcat & Y & Sep

        Next Y

    Else

        AConcat = AConcat & a & Sep

    End If

   

    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))

   

End Function

----------------------------------------------------------

ALT2: link

...........

Function ConcRange(Substrings As Range, Optional Delim As String = ",", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = True)
      
    Dim CLL As Range
    
    For Each CLL In Substrings.Cells
        If Not (SkipBlanks And Trim(CLL) = "") Then
            ConcRange = ConcRange & Delim & IIf(AsDisplayed, Trim(CLL.Text), Trim(CLL.Value))
        End If
    Next CLL
     
    ConcRange = Mid$(ConcRange, Len(Delim) + 1)
     
End Function

Smart solution by Oscar

Problem: 

I have table similar like this:

BatTodor

A 5
B 2
C 1
D 4
Is it possible with formula to generate list like this:
A
A
A
A
A
B
B
C
D
D
D
D

Solution

Array formula in cell A6:

=INDEX($A$1:$A$4,MATCH(FALSE, COUNTIF($A$5:A5,$A$1:$A$4)=$B$1:$B$4,0))

Source: link

Calculate work hours

Good to know

Cell E1:H8

Day	Start	End	Hours
Sunday			0:00
Monday	08:00	18:00	10:00
Tuesday	08:00	18:00	10:00
Wednesday	08:00	18:00	10:00
Thursday	08:00	18:00	10:00
Friday	08:00	18:00	10:00
Saturday	08:00	13:00	5:00

Cell A1:C4

Start	End	Hours
2009-02-14 Sat  08:00	2009-02-15 Sun 13:00	05:00
2002-09-09 Mo 12:00	2012-09-12 Thu 03:00	26:00
2001-01-21 Sun 08:00	2001-01-22 Mon 15:51	07:51

=SUMPRODUCT(INT((WEEKDAY(A2-{1;2;3;4;5;6;7})+INT(B2)-INT(A2))/7),H$2:H$8)-MEDIAN(0,MOD(A2,1)-INDEX(F$2:F$8,WEEKDAY(A2)),INDEX(H$2:H$8,WEEKDAY(A2)))-MEDIAN(0,INDEX(G$2:G$8,WEEKDAY(B2))-MOD(B2,1),INDEX(H$2:H$8,WEEKDAY(B2)))

Reference: link (+Excel file)

=N(OFFSET({array})) formula

Good to remember

Both N() and T() have other more important uses, they can be used to produce a usable array from Offset(Ref,{Array},0) that can be processed by other worksheet functions

Ex Assuming we have Text data from A1:A10
=Counta(Offset(A1,{1,5,6},0)) Array entered will give 1
However
=Counta(T(Offset(A1,{1,5,6},0))) will give 3
Like wise N() for numbers

Reference: link

SmartPivot - Multi charts from one Pivottable

Smart add-in:

Reference: linkhttp://rquintino.wordpress.com/2012/03/29/smartpivot-for-excelnow-for-the-new-olap-features/

Picture into Excel from imageurl

Dim url_column As Range
Dim image_column As Range

Set url_column = Worksheets(1).UsedRange.Columns("A")
Set image_column = Worksheets(1).UsedRange.Columns("B")

Dim i As Long
For i = 1 To url_column.Cells.Count

  With image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value)
    .Left = image_column.Cells(i).Left
    .Top = image_column.Cells(i).Top
    image_column.Cells(i).EntireRow.RowHeight = .Height
  End With

Next

Reference: link