Excel – Euclidean distance between two points with coordinates stored as strings

microsoft excel

I need to find the Euclidean distance between two points. I have the concatenated coordinates in a single cell. Each set of coordinates is like (x1,y1,z1) and (x2,y2,z2).

How can I do this in Excel?

Best Answer

Try the following User Defined Function (UDF):

Public Function distance(s1 As String, s2 As String) As Double
    Dim zum As Double, i As Long
    ary1 = Split(s1, ",")
    ary2 = Split(s2, ",")

    zum = 0
    For i = 0 To 2
        zum = zum + (CDbl(ary1(i)) - CDbl(ary2(i))) * (CDbl(ary1(i)) - CDbl(ary2(i)))
    Next i

    distance = Sqr(zum)
End Function

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:

=distance(A1,A2)

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!

Here is an example:

enter image description here

Related Question