+ Reply to Thread
Results 1 to 6 of 6

separate data from one cell into two separate cells question

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Office 2013
    Posts
    885

    separate data from one cell into two separate cells question

    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!
    Last edited by lilsnoop; 12-19-2012 at 05:54 PM.

  2. #2
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: separate data from one cell into two separate cells question

    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.
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  3. #3
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Office 2013
    Posts
    885

    Re: separate data from one cell into two separate cells question

    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.

  4. #4
    Forum Contributor
    Join Date
    10-31-2010
    Location
    london
    MS-Off Ver
    Excel 365
    Posts
    149

    Re: separate data from one cell into two separate cells question

    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)

  5. #5
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: separate data from one cell into two separate cells question

    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.

  6. #6
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Office 2013
    Posts
    885

    Re: separate data from one cell into two separate cells question

    Thanks a lot to the both of you!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1