Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Extracting letter from a column

Krish_Penubarthi
7 - Meteor

Hi,

 

I have a column from which I need to extract letter based on white spaces.

the output depends on the white spaces in the input column.

 

Eg: 

Input                                 Output

Green                                GR

Sky Blue                            SB

Deep Impact blue              DIB

 

How can this be achieved.

 

Thanks.

 

5 REPLIES 5
BenMoss
ACE Emeritus
ACE Emeritus

So you want the first character of each word?

 

There's an array of methods you could use to do this, but here is a RegEx and summerize tool solution...

 

Ben

 

 

 

 

Krish_Penubarthi
7 - Meteor

But, How can I get first 2 characters when it is a Single word like Bob in your example.

afv2688
16 - Nebula
16 - Nebula

Use this formula:

 

IF !CONTAINS(TRIM([Field1]),' ') THEN Left(TRIM([Field1]),2)
ELSE REGEX_Replace(TRIM([Field1]),'(\w{1}).*?\s?(\w{1}).*?\s(\w{1}).*','$1$2$3')
ENDIF

 

Cheers!

afv2688
16 - Nebula
16 - Nebula

And like this if they have to be uppercase:

 

IF !CONTAINS(TRIM([Field1]),' ') THEN Uppercase(Left(TRIM([Field1]),2))
ELSE Uppercase(REGEX_Replace(TRIM([Field1]),'(\w{1}).*?\s?(\w{1}).*?\s(\w{1}).*','$1$2$3'))
ENDIF

 

If there are more than 3 words just add: (\w{1}).*?\s?  

 

IF !CONTAINS(TRIM([Field1]),' ') THEN Uppercase(Left(TRIM([Field1]),2))
ELSE Uppercase(REGEX_Replace(TRIM([Field1]),'<here>(\w{1}).*?\s?(\w{1}).*?\s(\w{1}).*','$1$2$3$4'))   (and the dollar-4 , dollar5 and so on sign)
ENDIF

 

one per extra word needed :)

 

Cheers

BenMoss
ACE Emeritus
ACE Emeritus

Personally I would just try and understand the amount of spaces in the word and then create a seperate stream to deal with that...

 

So you can use a filter tool with the statement...

 

REGEX_CountMatches([Field1],"\s") = 0

 

To create two streams, one for words with spaces, one for words with no spaces, then on the stream with no spaces you can then just use a left([field],2) statement; and apply the same logic as before to the 2nd stream.

 

Example attached.

 

2019-04-08_11-06-12.png

 

Ben

Labels