I’ve been relying on the Advanced logic options in ArcMap’s Field Calculator to wrap up Natural Earth Vector. Because of the diacritical (accent) marks present on many placenames around the world, care must be taken to ensure they don’t get corrupted.
With basic SHP files, I often edit thematic data in Excel and then join it back with the SHP. However, Excel mangles diacritical marks, especially if you’re going cross platform between the Mac and PC. Even when everything is setup right, ArcMap the get Info panel displays ? marks for many diacriticals. The attributes table view shows them correctly, though.
Much of the base data and name attributes are compiled in Adobe Illustrator and exported with MaPublisher using custom scripts I’ve written to take the name from the text that’s been grouped with the feature. This allows contributors who don’t have ArcGIS at home or in the office to take part in the project. Because we often have other attributes (maybe a second name, scale rank, type of feature), I use an “_” underscore character to concatenate these into one string. This string then needs to be parsed back into separate attribute columns.
Note: SHP files use Windows 1252 character encoding. If you’re on a Mac, change your MP export options from “System” to that. If you’re on a PC, you’re already good.
Splitting strings:
In VBA prelogic area (advanced checkmark on):
myString = [ColumnName]
myPosition = InStr(myString , “_”)
myLeft = myPosition – 1myLen = Len(myString)
myRight = myLen – myPosition
country = Left( myString, myLeft )
provNumber = Right( myString, myRight )
In the field results area: provNumber or country.
Note: If the result is destined for a number formatted column, either caste provNumber as a number or use a temporary holding column that is string formatted, then rerun the field calculator on the number formatted column deriving the value from the temp field. ArcMap with auto-caste for you in that case.
After the jump: Find and replace, counting substrings, hit tests, and changing case.