separate data from one cell into two separate cells question

**lilsnoop** · 12-19-2012, 05:08 PM

Hello!

I'm looking for assistance using vba code to separate data from column F, from cell F2 down (if data exists) so it will then populate the latitude data in column separate the data in Column/cell I2 down and longitude in column/cell J2 down.

This represents the type of data that will be in column F, starting in cell F2 down.
Lat:42.0994249 Lng:-86.4305470
Lat:42.1215380 Lng:-86.3941520
Lat:42.1167065 Lng:-86.4541894
Lat:42.1268540 Lng:-86.3962449
Lat:42.0860640 Lng:-86.4197156
Lat:42.1219330 Lng:-86.4303490
Lat:42.0838967 Lng:-86.4415145
Lat:42.1243350 Lng:-86.3704180

Ideally I need the the "42.0994249" portion only to be placed in cell I2
and the "-86.4305490" in cell J2. The "Lat:" and "Lng:" should be ignored. It is for this reason I suspect vba coding to aid in this venture rather than a formula based solution. Any assistance would be greatly appreciated!

**Miraun** · 12-19-2012, 05:11 PM

If you're running 2007 like your profile says, use the Text-to-columns function under teh data ribbon. Deliminated with SPACE and SemiColon and you should have some nicely separated data.

No reason to make it complex with a macro.

**lilsnoop** · 12-19-2012, 05:23 PM

Hi Miraun! I guess I should have mentioned that the data shown above is based from a formula. So if I clicked on cell F2, the data shown in my earlier example would actually look like this

Please Login or Register  to view this content.

**bopsgtir** · 12-19-2012, 05:31 PM

Like Miraun said, thats the easiest way, but this can still be done with formulas aswell

in I2 =MID(F2,5,FIND(" ",F2)-4)
in J2 =MID(F2,FIND(":",F2,5)+2,20)

**Miraun** · 12-19-2012, 05:49 PM

Alternatively, even if it's coming across as a formula like that... Can just Copy it, PASTE SPECIAL, and then select values, and it'll remove the =Lat_Lon stuff, and just leave the values that resulted from the formula. Then you're free to text-to-columns it if you want. That way you'll be left with just the numbers to mess with however you choose.