inserting characters into cells

[ash_the_fish]

Hi all,

This is my first post as a new member. I apologise if I've posted this into the wrong topic area - I'm not sure if this is a programming question...

I have recently purchased a new GPS for my fishing boat. I am trying to transfer my old coordinates from one unit to the other. I am using a .csv file to achieve this. Can some kind please let me know (and possibly tell me) if there is way to insert characters and spaces in to multiple cells. I have over 800 individual coordinates to modify to the new gps and this would take a long time to achieve individually.

I need to replace this: 12.34.567 to this: 12 34.567 N

and this: -1.23456 to this: 001 23.456 W

MANY MANY thanks in advance.

Regards,
Ashley

[NBVC]

Maybe you can give a more extensive sample database with expected results to show if there are other possibilities.. also how do you know if it is N, S, E, or W...

[ash_the_fish]

Thanks for your prompt reply. The spreadsheet is set up with 6 columns; longitude, latitude, date created, comments, depth and symbol.

There are currently 873 rows of data. these rows represent one waypoint or coordinate. I only need to change the layout of two cells in one row. Those are the example I gave above. I know they are North and South coordinates - I use them daily plus it is my geographical location.

I hope this helps.....

Thanks

[NBVC]

So is it safe to assume that if the number has to decimal points like:

12.34.567

then you just want to replace the first with a space?

and if the number has one decimal point like:

-1.23456

then you want to convert the decimal to a space and add a decimal 3 digits from the right? (and when do we add the two 0's up front? when it is negative or always?)...

.. also I meant how do we know if we should be appending an N, W, E or S to the result?

[ash_the_fish]

Hi NBVC, thanks once again for your promptness. I've answered below in red

So is it safe to assume that if the number has to decimal points like:

12.34.567

then you just want to replace the first with a space? YES

and if the number has one decimal point like:

-1.23456

then you want to convert the decimal to a space and add a decimal 3 digits from the right? YES(and when do we add the two 0's up front? when it is negative or always?)... ALWAYS, but replacing '-' with '00'

.. also I meant how do we know if we should be appending an N, W, E or S to the result? I understand, sorry. The latitude numbers and longitude numbers are in different cells. It is therefore (hopefully) easy to add N after the last space in one cell and then W in the other.

Thank you.

[NBVC]

In a hurry to leave...but this is what I've got so far... perhaps can be improved

Please Login or Register  to view this content.

where A1 contains original, copied down.

[ash_the_fish]

SPOT ON!!!

But I also need to add a space and then the letter N after the last number.

i.e 12 34.567 becomes this: 12 34.567 N


thanks. ashley

[oldchippy]

Hi,

Try this

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=2,SUBSTITUTE(A1,"."," ",1),REPLACE(SUBSTITUTE(SUBSTITUTE(A1,"."," "),"-","00"),LEN(SUBSTITUTE(SUBSTITUTE(A1,"."," "),"-","00"))-2,0,".")) &" N"

[NBVC]

SPOT ON!!!

But I also need to add a space and then the letter N after the last number.

i.e 12 34.567 becomes this: 12 34.567 N


thanks. ashley

This is what I was asking. What determines if it should be an "N" or "W" or other?

[ash_the_fish]

Special thanks to NBVC and oldchippy