Trim or RegEx: How to remove string after special character

[JoshuaBova]

I have a list of addresses that need to have the Suite numbers removed. See Below

What I have	What I need
Address	Address
5077 SABRE LN # A	5077 SABRE LN
3210 FALLMEADOW ST # 127A	3210 FALLMEADOW ST
721 S INTERSTATE 35 E # 144	721 S INTERSTATE 35 E

What is the best way to do this? Thanks!

[MSalvage]

@JoshuaBova,

Try inserting the regex tool with output method set to parse using this Expression:

(.*)\#.*

Best,

MSalvage

[derekbelyea]

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.

[danrh · Accepted solution]

Another option:

Left([Address], FindString([Address], '#'))

Drop it in a Formula tool and choose to update the Address field.

[JoshuaBova]

This worked great. Thanks!!!

[r9dayts]

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)

[Usamah22]

Hi,

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?

[danrh · Accepted solution]

@Usamah22, you can use either RegEx or SubString to do the trick:

REGEX_Replace([YourField], '....(.*)', '$1')

Substring([YourField],4,Length([YourField])-4)

@r9dayts, sorry this comes (very) late, but try out this formula:

REGEX_Replace([YourField], '.*\#(.*)', '$1')

[Usamah22]

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%.

10-15%

80-85%

0-5%

[danrh]

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