Euclidean distance between two points with coordinates stored as strings

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?

2 Answers

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:

and

(v=office.14).aspx

and for specifics on UDFs, see:

Macros must be enabled for this to work!

Here is an example:

=sqrt(power(x2-x1,2)+power(y2-y1,2)+power(z2-z1,2))

Or as the Wikipedia article states:

Edit: Saw after the fact that you had concatenated cells. That complicates things a bit. If you can separate the 3 parts of each coordinates, this is trivial. Otherwise, I need to know a little more about what range of numbers we may be talking about? (I'm thinking of a little =left() =mid() =right magic.)