This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a list of addresses that need to have the Suite numbers removed. See Below
What is the best way to do this? Thanks!
Go to Solution.
Go to Solution.
Try inserting the regex tool with output method set to parse using this Expression:
If you prefer not to use REGEX functionality then this can be solved another way. Use the # character as a token for splitting the address field using the TEXT TO COLUMNS tool.
Taking this one step further you can convert embedded text strings with the equivalent meaning (eg UNIT, APT, SUITE etc) to the # character using the FIND AND REPLACE TOOL.
Both of these techniques are illustrated in the example below and in the atttached file.
Left([Address], FindString([Address], '#'))
Drop it in a Formula tool and choose to update the Address field.
This worked great. Thanks!!!
Is there a way to take what is after the #, i.e., start at end of the line?
I have succeeded in achieving my goal by using the following, but I would guess it could be done pretty quickly in regex, just cant quite figure out how to start the parsing at the end of the word.
1. Formula: ReverseString(Column)
2. REGEX: (.*)\#.*
3. Formulat: ReverseString(RegExOUt1)
trying to trim the first 4 characters from a string. For example:
Convert 201D11000885Y to 11000885Y
the remaining string can vary in length. Is substring the best thing to use?
@Usamah22, you can use either RegEx or SubString to do the trick:
REGEX_Replace([YourField], '....(.*)', '$1')
@r9dayts, sorry this comes (very) late, but try out this formula:
REGEX_Replace([YourField], '.*\#(.*)', '$1')
Thanks for this. I've got another string thing!
How can i isolate the figures below? i need to separate the figures so i can take the average of the two. For example 10-15% would become 12.5%.
You could do this using a few different methods (i.e. using the Text To Columns tool) - but if you want an all-in-one formula, try something like this:
(ToNumber(REGEX_Replace([YourData], '(\d+)-(\d+)', '$1'))+ToNumber(REGEX_Replace([YourData], '(\d+)-(\d+)', '$2')))/2