Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Trim or RegEx: How to remove string after special character

JoshuaBova
5 - Atom

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

 

What I haveWhat I need
AddressAddress
5077 SABRE LN # A5077 SABRE LN
3210 FALLMEADOW ST # 127A3210 FALLMEADOW ST
721 S INTERSTATE 35 E # 144721 S INTERSTATE 35 E

 

What is the best way to do this? Thanks!

11 REPLIES 11
MSalvage
11 - Bolide

@JoshuaBova,

 

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

 

(.*)\#.*

 

JoshuaBova Regex.PNG

 

Best,

MSalvage

 

derekbelyea
12 - Quasar

 

 

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.

 

 

2018-01-20_00290.png

danrh
13 - Pulsar

Another option:

 

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

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

 

image.png

JoshuaBova
5 - Atom

This worked great. Thanks!!!

r9dayts
5 - Atom

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
8 - Asteroid

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
13 - Pulsar

@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
8 - Asteroid

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
13 - Pulsar

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

Labels