Extracting letter from a column

[Krish_Penubarthi]

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.

[BenMoss]

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]

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

[afv2688]

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 · Accepted solution]

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 · Accepted solution]

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.

Ben