Excel: I want to extract characters from 1 column and paste these into a separate column

Question

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:

You mean to say you want to extract Latitude and Longitude from Column C in Column D (Latitude) and E (Longitude) ? — Rajesh Sinha, Feb 23 '18 at 11:48
I just want to extract the Latitudes and Longitudes from Column C — wilga, Feb 23 '18 at 11:49
@wilga,, just check I've posted a single Formula Solution. Hope you find is useful. — Rajesh Sinha, Feb 23 '18 at 12:40

score 11 · Answer 1 · answered Feb 23 '18 at 12:04

11

Select the whole column and copy it on the next empty column, suppose it is columnE.
Select Column E.
Go to Data -> Text To Columns.

Select Delimited

Select Comma,

Click Next and Finish

answered Feb 23 '18 at 12:04

jcbermu

17,278
2
52
60

score 4 · Answer 2 · answered Feb 23 '18 at 11:50

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.

IQV · Answer 3 · 2018-02-23T12:02:50.773

3

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))

edited Feb 23 '18 at 12:02

answered Feb 23 '18 at 11:49

IQV

666
4
12

score 2 · Answer 4 · answered Feb 23 '18 at 12:19

This single Formula will extract both Latitude and Longitude.

In Cell D2 write this Formula.

=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.

Excel: I want to extract characters from 1 column and paste these into a separate column

4 Answers4