Closed Thread
Results 1 to 3 of 3

replace string based on wildcard and character count

  1. #1
    Registered User
    Join Date
    01-26-2020
    Location
    Amsterdam
    MS-Off Ver
    16
    Posts
    17

    replace string based on wildcard and character count

    Dear members, I am new to this forum. I have posted this one also on the general excel thread, but maybe this was wrong because I realised my question is related to functions. Apologies if I am creating a problem if I post this same question also the this thread. Member BMV also helped me a bit in the other thread.

    I have with a large dataset (> 8000 rows), which needs to be modified. In this dataset, I have kml coding (Google Earth) that needs to be converted to geojson code. I have come pretty far so far, but this problem gives me a headache:

    The kml format has 3 sectors for each coordinate seperated by comma's: lon,lat,accuracy. So it looks like this: 44.980061,-16.200435,20.32. Here, accuracy is 20.32. The accuracy can have a different number of digits for example 20,32 or 0.0. For example: 45.036191,-16.217396,0.0 or 44.980061,-16.200435,20.32. I have a very long string of these kml codes in each cell (to create polygons). All lon,lat,accuracy coordinates are seperated by a space (" ") so the format is like this: 44.980061,-16.200435,20.32 45.036191,-16.217396,0.0, etc, etc. Now the problem: For the geojson, I need to remove the accuracy string. So, I basically need: 44.980061,-16.200435 45.036191,-16.217396 etc, etc. In the attached excel, I give a few examples.

    My question is, how can I get rid of those accuracy digits? The difficulty is that the accuracy string can have variable lengths. My first thought is:

    1) Search for the spaces (" ") between two coordinates
    2) Search back (to left) to find the first comma in the coordinate
    3) Count the number of digits between the space and first comma
    4) Remove those digits and replace it by a space (" ")

    I tried working with SEARCH, SUBTITUDE, LEFT/MID etc, but I can't find the right code to make it work. Any help is very much appreciated! Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: replace string based on wildcard and character count

    Kievet, you really should not open a second post on the same subject (forum rule #5). Please continue on your other post.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: replace string based on wildcard and character count

    Administrative Note:

    Welcome to the forum.

    Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

    Please see Forum Rule #5 about thread duplication.

    I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-gen...ml#post5267470

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 12
    Last Post: 02-07-2020, 03:51 AM
  2. String too long for VBA. Can it be split based on character count?
    By icyrius in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 07-08-2016, 01:41 PM
  3. replace EACH Nth occurrence a character in a string with another character
    By mattman7 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-23-2016, 12:23 PM
  4. Add Rows based on string character count then Merge and format- Improve my Macro, please
    By Pierce Quality in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-20-2015, 01:41 PM
  5. [SOLVED] Split string based on character count
    By cedric_dranreb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2013, 05:14 AM
  6. Replace Wildcard with Same Character
    By jmeggers in forum Excel General
    Replies: 6
    Last Post: 10-11-2011, 09:28 PM
  7. Replies: 8
    Last Post: 08-17-2005, 09:05 PM

Tags for this Thread

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