Help needed...

[BB]

I have a cell that contains the following info, 32°19' 786N-108°33' 627W.
Is there a formula that will remove partial data from a cell. For example,
I need to remove everything after the N. Any help would be appreciated.

 

[Don Guillett]

A meaningful subject line would be helpful for the archives.
Try this in a formla

=LEFT(M1,FIND("N",M1))

 

[JE McGimpsey]

One way:

=LEFT(A1,FIND("N",A1))

 

[BB]

32°19' 786N-108°33' 627W
32°19' 688N-108°33' 576W
32°19' 590N-108°33' 525W
32°19' 493N-108°33' 475W
32°19' 394N-108°33' 425W
32°19' 297N-108°33' 374W
32°19' 199N-108°33' 323W
32°19' 100N-108°33' 272W
32°19' 002N-108°33' 222W
32°18' 905N-108°33' 170W


The data to the right of the "N" needs to be removed and placed into another
cell. When I use the formula =LEFT(A1,FIND("N",A1)) I get an error that
refers to a "circular reference". The range of cells is from a1-a7330.
What am I doing wrong? Sorry for the original 'subject' line.

 

[Don Guillett]

You would have put the formula in the 1st adjacent cell and copied down.
But, this would be better.
Sub extractdata()
application. screenupdating=false
application.calculation=xlmanual
For Each c In Range("a1:a7330")
c.Offset(0, 1) = Left(c, InStr(c, "N"))
Next
application. screenupdating=true
application.calculation=xlautomatic
End Sub

 

[Harlan Grove]

You would have put the formula in the 1st adjacent cell and copied down.
But, this would be better.
Sub extractdata()
application. screenupdating=false
application.calculation=xlmanual
For Each c In Range("a1:a7330")
c.Offset(0, 1) = Left(c, InStr(c, "N"))
Next
application. screenupdating=true
application.calculation=xlautomatic
End Sub

...

Perhaps, but if the dashes in the OP's data were just separators rather than
minus signs, Data > Text to Columns, Delimited, using dash as the delimiter
(check Other and enter a dash in the entry field to the right) would do what the
OP wants without having to write a macro.

 

[Don Guillett]

A good point

 

[BB]

Thanks to both of you, that worked out well. Now is there a quick and easy
way to convert the Lat/Long info from degree, minute and seconds to a
decimal format?
Example: 32°19' 786N Latitute would appear as 32.19786
-108°33' 627W Longitute would appear as -108.33627

 

[Don Guillett]

This should be of help
http://tinyurl.com/3b87w
more
http://tinyurl.com/2449a