Working with strings in VBA can look complicated at first glance, but it’s quite simple once you begin to think of strings as a sequence of numbers representing the position of each character. If your string is 10 characters long and the information you need is represented by characters 4,5,6 &7, then writing your code becomes about isolating those characters. Before I dive into what I did, let me first give a brief explanation of each VBA function I used.
- Mid – you might recognize this formula from Excel if you’re familiar with Excel’s text formulas. The mid formula allows you to return a given length of text from a string from a starting point you specify.
- Instr – if you’re completely new to VBA, this is probably something you’ve never seen. The InStr function returns the numbered position of the first occurrence of a character within a string after a given starting point.
- Example – InStr(1,”Apple:Orange”,”:”) would return the value 6 since the colon is the 6th character.
- Len – this is the simplest function as it returns the total number of characters within a string.
After examining the format of the location column, I could see that the two pieces I wanted were contained inside of parenthesis and separated by a comma. Knowing this, I could use the comma to determine the length of each of my strings I wanted to return.
For latitude, I needed only the first number so I started from the second position (skipping the parenthesis) and determined the length of my string by using the InStr function and subtracting 2 (2 because of the comma and the starting position of 2).
For longitude, I needed the second number. Therefore, I start at the comma using InStr function and adding 1 (adding 1 due to the space that separates. You could also have left this alone and used a Trim function). I determine the length by using the Len function to get the full length of the string and subtract the starting point and one additional character to remove the parenthesis.
I hope at least one VBA code example is useful for you. If you want the working file I used for the examples, don’t forget to subscribe to my site for access to that file and all my other example files and free Excel templates! I’ll never share your information and won’t spam you with dozens of emails. If you have questions about the content, post in the comment section or shoot me an email at firstname.lastname@example.org. I look forward to hearing from you!