• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

extract the coordinates from column f to paste them into columns b and c

Good morning.
I have an excel file that contains addresses in column a. I'm using a system to obtain the geographical coordinates online which I copy next (coonna f of the file). I would like to insert in column "b" and "c" formulas like an index compare which by associating the two addresses then copy the geographical coordinates from column "f" and insert them in column "b" and "c".
Is it possible to fre so that the coordinates are pasted as text to avoid losing formatting?
Thank you
 

Attachments

  • Cartel1.xlsx
    10.4 KB · Views: 6
Sa formulas you could use in B2:
=LEFT(RIGHT(F2,LEN(F2)-FIND(":",F2,1)-1),FIND(",",RIGHT(F2,LEN(F2)-FIND(":",F2,1)-1),1)-1)
and in C2:
=RIGHT(F2,LEN(RIGHT(F2,LEN(F2)-FIND(":",F2,1)-1))-FIND(",",RIGHT(F2,LEN(F2)-FIND(":",F2,1)-1),1)-1)
then pull down both as needed.
 
Thank you for your intervention. Unfortunately it is not what I was looking for because your formulas provide that the ways are equal on the same line instead it is not so. You should look for the same way as column a in f and then with the same way extract the coordinates. In addition, sometimes the coordinates do not have a space after the comma and come out cut. Maybe you should use a function that finds the comma and from there on extrapolates etc.
 
What bad luck, it would have been better if we had had a file with the right cases.
If it were only for uneven spacing, then in B2 use:
=TRIM(LEFT(RIGHT(F2,LEN(F2)-FIND(":",F2,1)),FIND(",",RIGHT(F2,LEN(F2)-FIND(":",F2,1)),1)-1))
and in C2:
=TRIM(RIGHT(F2,LEN(RIGHT(F2,LEN(F2)-FIND(":",F2,1)))-FIND(",",RIGHT(F2,LEN(F2)-FIND(":",F2,1)),1)))
 
What bad luck, it would have been better if we had had a file with the right cases.
If it were only for uneven spacing, then in B2 use:
=TRIM(LEFT(RIGHT(F2,LEN(F2)-FIND(":",F2,1)),FIND(",",RIGHT(F2,LEN(F2)-FIND(":",F2,1)),1)-1))
and in C2:
=TRIM(RIGHT(F2,LEN(RIGHT(F2,LEN(F2)-FIND(":",F2,1)))-FIND(",",RIGHT(F2,LEN(F2)-FIND(":",F2,1)),1)))
There is not only to extract the coordinates from column F but extract them and insert them at the same way. Streets are not in the same row
 
Back
Top