This question already has an answer here:

Looking at the image below I want to extract the Lats and Longs from the "geohash,latitude,longitude" column.

I can't just use the function RIGHT(C2,LEN(C2)-8) because the Lats & Longs are different lengths and I'll have to go through the entire column E manually adding any numbers that have been removed.

Example picture:

marked as duplicate by Graham Wager, LPChip, Máté Juhász, bertieb, Kamil Maciorowski Feb 24 at 10:02

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • You mean to say you want to extract Latitude and Longitude from Column C in Column D (Latitude) and E (Longitude) ? – Rajesh S Feb 23 at 11:48
  • I just want to extract the Latitudes and Longitudes from Column C – wilga Feb 23 at 11:49
  • @wilga,, just check I've posted a single Formula Solution. Hope you find is useful. – Rajesh S Feb 23 at 12:40
  • Can you / would you write a VBA function? – Barranka Feb 23 at 21:56
  1. Select the whole column and copy it on the next empty column, suppose it is columnE.

  2. Select Column E.

  3. Go to Data -> Text To Columns.

enter image description here

  1. Select Delimited

enter image description here

  1. Select Comma,

enter image description here

  1. Click Next and Finish

You can use FIND, MID & LEFT Functions for this.

Use this for Lat:

=LEFT(MID(C1,FIND(",",C1)+1,100),FIND(",",MID(C1,FIND(",",C1)+1,100))-1)

& Use this for Long:

=MID(MID(C1,FIND(",",C1)+1,100),FIND(",",MID(C1,FIND(",",C1)+1,100))+1,100)

Then simply drag them both down.

If you want to return them as Integers, use +0 at the end of the formulas, else they will be strings.

You can use the FIND()-function to get the position of the commas and after that split the string for latitude and longitude.

So can use for latitude
=MID(A2, FIND(",", A2)+1, FIND(",", A2, FIND(",", A2)+1)-1)

and for longitude
=RIGHT(A2, LENGTH(A2)-FIND(",", A2, FIND(",", A2)+1))

This single Formula will extract both Latitude and Longitude.

In Cell D2 write this Formula.

enter image description here

=TRIM(MID(SUBSTITUTE($C2,",",REPT(" ",999)),COLUMNS($C:C)*998,999))

NB: Finish with Enter you find the Latitude then just drag or Copy the Formula to adjacent Cell (one cell Right) you get Longitude and finally drag it down till is necessary.

I do believe this help you.

Not the answer you're looking for? Browse other questions tagged or ask your own question.