Subscribe Bookmark
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

If you learn only one Formula Editor function, Word() is the one

Just look at the list of Character functions available in the JMP Formula Editor.

Not that anyone is counting, but that's 42 functions! Needless to say, if you've got a character column and want to change it somehow, JMP probably has a function to do it for you.

Notice, though, that nine of those functions are all – or mostly – about extracting some portion of a character value. Admittedly, this is one of the most common operations – extracting some part of a larger string of characters. This is known as a substring.

The most obvious function for this is the Substr() function, but this function requires that you know exactly where the part you're interested in lies in the larger string. It also means that the part you want has to be in the same place in every string.

That's why I love the Word() function. It's amazingly flexible and works the way that I think.

The Word() function takes two required arguments and one optional.

Word(n, "text", <"delimiters">)

The Word() function divides the text up into words based on the delimiters you specified and returns the nth word. If you don't specify the delimiters argument, then the default delimiter is the same one we use to separate words in sentences: a space.

As an example, consider the following column of days:

Day
December 6
December 16
September 28
May 19
September 29
September 11
March 14
November 13
October 27
January 7

Separating the month is easy with the Word() function. You just want the first word of each string.

Word( 1, Day )

Month
December
December
September
May
September
September
March
November
October
January

That works well in many cases, but the real power comes when you think of using other characters as delimiters to divide a string up into "words."

Here's another list. These are locations. This time the latitude and longitude is embedded in the string behind the city name.

Location
1600 NEW BERN AVE Raleigh, NC [35.779892815000494, -78.61572674699966]
3800 NEW BERN AVE Raleigh, NC [35.79777009600048, -78.57633099599968]
3200 CAPITAL BLVD Raleigh, NC [35.822875239000496, -78.58848179699964]
700 VARDAMAN ST Raleigh, NC [35.77016025800049, -78.61995064499968]
2900 BASSWOOD DR Raleigh, NC [35.74121031600049, -78.60477047599966]
1300 CURFMAN ST Raleigh, NC [35.76619700500049, -78.65143264899967]
8500 SWARTHMORE DR Raleigh, NC [35.89068092800045, -78.60012101699965]
1200 CANYON ROCK CT Raleigh, NC [35.76156160400046, -78.55683519199965]
800 GREENWICH ST Raleigh, NC [35.768321829000456, -78.59393748999963]
300 ROSELLE CT Raleigh, NC [35.788000157000454, -78.58367178499964]

It would be useful to have latitude and longitude in another column.

If you think of the square brackets as delimiters, this is pretty simple. You just want the second "word."

Word(2, Location, "[]")

Now you've got a column (LatLong) of strings that have latitude and longitude separated by a comma and a space.

LatLong
35.779892815000494, -78.61572674699966
35.79777009600048, -78.57633099599968
35.822875239000496, -78.58848179699964
35.77016025800049, -78.61995064499968
35.74121031600049, -78.60477047599966
35.76619700500049, -78.65143264899967
35.89068092800045, -78.60012101699965
35.76156160400046, -78.55683519199965

The Word() function comes to the rescue again. This time, we want to use comma and space as the delimiters. If we use only a comma, then the longitude string will have a space at the start. Using both delimiters gives us strings with no space.

Latitude

Word(1, LatLong, ", ")

Longitude

Word(2, LatLong, ", ")

These should be cleaned up further by converting them to numbers using the Num() function, but that's an exercise for another post.

The key to taking advantage of the Word() function is to view your strings as "sentences" and think abstractly about choosing delimiters.

Here's a final example. Suppose you have a column of lot numbers (LotNo) with a name embedded in the middle:

LotNo
A30941ALTE69073172
A82886ILEAK51521781
A54715XSURVXC88997379
A7860BATTBC97523479
A79004ILEAK52159080
A59508ABAEG58962051
A44172ILEAK28255713
A21319XSURVXC88014800
A58308BATTBC49519851
A19412C83938451

This is difficult since the name doesn't occur in the same position in the string in each row. If you think of the numbers the same way you think of spaces when reading, then the name becomes the "second" word.

Word(2, LotNo, "0123456789")

NAME
ALTE
ILEAK
XSURVXC
BATTBC
ILEAK
ABAEG
ILEAK
XSURVXC
BATTBC
C

If you have time to learn only one of the 42 character functions in the formula editor, I'd start with the Word() function.

Update (11:09 AM, 12/4/2013): The last table in this post has been updated to correct a mistake introduced in editing.

5 Comments
Community Member

Paul Savarese wrote:

Jeff: Enjoyed your post about the WORD function but got a little confused by the last example. It seems something is out of place. The first Lot No is A30941ALTE69073172 and the imbedded 'word' is, apparently, ALTE yet the Word(2, LotNo, "0123456789") is shown as returnng the value 'C'? Also, some of the other Lot No had imbedded words of 'XSURVX" but none of the results showed this being selected. Also, I did not see the word 'VOLT' imbedded in any of the Lot No but it appears as the result of the WORD function on two occasions?

Thanks,

Paul S.

Community Manager

Jeff Perkinson wrote:

Thanks, Paul. I've updated the post to correct that table.

Community Member

Yee-Seng Lee wrote:

Hi Jeff,

If I had a string = "1*2*3**5***8" where it is intended to be split into 8 separate columns instead of 5, which means the double ** should be regarded as one column with empty data, while triple *** should be regarded as two columns with empty data, so on and so forth, any workaround with the Word function?

Community Manager

Jeff Perkinson wrote:

Try the Item() function instead. It's just like Word() except it treats multiple delimiters as each defining a new item.

Community Member

Yee-Seng Lee wrote:

Thank you very much. It works perfect !

Article Tags