Help needed...

  • Thread starter Thread starter BB
  • Start date Start date
B

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.
 
A meaningful subject line would be helpful for the archives.
Try this in a formla

=LEFT(M1,FIND("N",M1))
 
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.
 
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
 
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
Click to expand...
...

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.
 
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
 
Back
Top