Import longitude and latitude from Text file

[micko1]

Need help please.
I have a program called GEOD that allows me to export lat and long to a CSV file ( notepad) This data is then converted to an excel spreadsheet. Problem is the longitude needs to be displayed with a negative symbol in front of it. I have tried formating the cell to custom "-## ## ##.#####" but because the numbers imported already have spaces in them the formating does not work. How can I change the imported data to display as requested.
Thanks in advance
Mick

 

[xenou]

What notation is used in the CSV file?
How is it imported into Excel?
Is it the same in Excel, or does the import cause any transformations in the representation of longitude?

Be aware that changing formatting is nothing but dressing up. If you actually need the values to include the negative sign, then you must actually change the data values, not just the way they look.

ξ

 

[micko1]

Thanks heaps for the reply. This is obviously a probelm I am trying to solve for someone else so can only relate what has been told to me. The data is in Notepad and then imported into excel and looks like this.
28 47 06.72189
There are other columns as well but this is the only data I need to change.
To get around the issue I have created 2 new columns one with the minus symbol in it "-" lets say it in column "A" and another with the following formula "=A1&C1" lets say "c1" contains the Longitude data. Then copy the formula down, and then copy the results, Values only to a new column. Seems a bit long winded but gets the desired results. If there is a easire, quicker way I would love to find it as there a hundreds of entries to import and convert.
Thanks again for your help.
Mick

 

[xenou]

^^ That's how I would go about this.

There's a slight "gotcha" in that Excel treats values that begin with a negative sign (by default) sometimes as the beginning of a formula (negation). This could be a problem - has anything cropped up in this regard?

Otherwise, you could devise a vba routine to handle this if its a common thing you have to do every day (it's not worth the trouble for one off situations).

P.S., what version of Excel are you using?

 

[Rick Rothstein]

In addition to xenou's questions, I have one... is your "number with spaces" really a number on the worksheet formatted with those spaces? Or are those "numbers" really text? You can tell very quickly by widening the column and noting what side of the cell it is on... rightside, it's a number... leftside, it's text. If it turns out to be text, did you want it to be a real number (that is, will calculations be done with it)?

 

[micko1]

It would appear that it is comming across as text as it is on the left. Tried reformating to number, Custom etc but nothing seemed to work.

Mick

 

[Rick Rothstein]

It would appear that it is comming across as text as it is on the left.

Yeah, that is what I thought might be the case as soon as I saw those spaces. Try this... first select the cells with your "text numbers" and then run the following macro and it should straighten everything out for you...

Sub MakeLongitudeTextIntoNumber()
  Dim Cell As Range
  For Each Cell In Selection
    Cell.Value = -Replace(Cell.Value, " ", "")
    Cell.NumberFormat = ";-#0 00 00.0000"
  Next
End Sub

 

[micko1]

Rick
Thanks heaps, that certainly saves a lot of time. I have given the solution to the guy concerned and he is over the moon. Thanks again for your assistance.

Mick