Hello! I have a problem with my subfield function even after browsing through the forum. I hope you can help me.
I have a column with addresses which looks like this:
somestreet 123, city but sometimes it will look like this:
some street name 123, city
Now what I want to do is to get rid of the number so in the end my rows will look like:
some street name, city and so on.
I have tried nesting my subfields but I cannot seem to get rid of the number. The problem is the various amount of blank spaces in the street name, sometimes there is none, sometimes there is 1 or more.
The furthest I have come is this:
SubField(SubField([FieldName], ' ', 1), ',', 1) & SubField([FieldName], ',', 2) AS [FieldName] but that of course results in the street names with spaces to be cut off and only show the beginning.
'somestreet123, city' works fine and becomes 'somestreet city' BUT
'some street name 123, city' becomes 'some city' which is wrong.
You can make use of the PurgeChar()-function to first get rid of the numbers. Then you can split the fields into two by using SubField() and lastly you can again use PurgeChar()-function to get rid of the spaces in the first field.