Thanks Peo and all who responded.
I did try the Text to Columns together with search/replace before.
But what I wanted was a dynamic way, as the table is going to be
built by web queries (see other article "parsing HTML table into
excel. How?". I should have written this at the first place.
there is a VBA approach from mojo that I'd like to try.

thanks a lot

Dan

"Peo Sjoblom" wrote in message
...
This worked for me using your example, assume the values are in column K,
insert
8 empty columns to the right of K, that is select columns L to S and do
insertcolumns
Select column K and do datatext to columnsfixed width, make sure all you
values are there,
click next and finish, now select column K and column O and do
editreplace,
place the cursor in the
find what box and hold down alt while typing 0176 on the numpad, release
Alt, leave replace with empty and select replace all
now select columns L and P, do the same but put an apostrophe in the find
what box, repeat once again for columns
M and Q but put a quotation mark in the find what box. That would give you
numbers in each column except for the N and the E i columns N and R

Now in a cell get the Latitude as follows

=K2+L2/60+M2/3600

copy down

for Longitude

=O2+P2/60+Q2/3600

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Dan" wrote in message
...
Hi there,

I do have a string in a cell that looks like this (this is UTM ED50
format):

46° 34' 21.09" N 8° 24' 54.28" E


I need to convert this string to Latitude and Longitude. The calculation
method is pretty simple. In the example above, its
46 + 34/60 + 21.09/3600 = 46.572525 (Lattitude)
8 + 24/60 + 54.28/3600 = 8.415077778 (Longitude)

So, the calculation as such is not very complicated.
What drives me nuts is to get the numbers out of the long ED50 string. I
managed this with a LOT of SEARCH() and MID() functions, but it's just
awfull !!.

Latitude


=MID($K2,1,SEARCH("°",$K2)-1)+(MID($K2,SEARCH("°",$K2)+1,(SEARCH("'",$K2)-SE


ARCH("°",$K2))-1))/60+(MID($K2,SEARCH("'",$K2)+1,(SEARCH("""",$K2)-SEARCH("'
",$K2))-1)/3600)
Longitude


=MID(MID($K2,22,15),1,SEARCH("°",MID($K2,22,15),1)-1)+MID(MID($K2,22,15),SEA


RCH("°",MID($K2,22,15),1)+1,SEARCH("'",MID($K2,22, 15),1)-SEARCH("°",MID($K2,


22,15),1)-1)/60+MID(MID($K2,22,15),SEARCH("'",MID($K2,22,15),1) +1,SEARCH("""
",MID($K2,22,15),1)-SEARCH("'",MID($K2,22,15),1)-1)/3600

Anyone knows a simpler way?

Thanks

Dan