Add Final Punctuation

Support and Helpdesk Data cleaning and transformations
1

How can I add final punctuation to all entries within a column? This is a pretty straightforward question, and I am disappointed that I couldn’t figure it out on my own. I just need to be able to add punctuation to the end of each cell within a particular column in my spreadsheet.

For example, I need to change something like:
“Hello world”
to “Hello world.”

Thanks for the help.

2

You can join a string to a string using +

So if your cells all contain strings and you want to add a period / full stop in all cases then you can use

value+"."

If you have more complex rules for when to add punctuation and what punctuation to add then please ask

1 Like
3

Thanks for this. If you don’t mind a follow-up question, what would it look like if I only wanted to add punctuation to strings that do not already end in final punctuation (period, exclamation, or question marks)?

4

Additionally. It would be good to be able to remove final punctuation.

5

There are a few ways you could do this. You can test whether a string ends with a particular character using endsWith(). For example:

value.endsWith(".")

will return true if the value in the cell ends with a period, and false otherwise. If you wanted to test for a single piece of punctuation this would do the trick. However, since you want to test for several it might be easier to use the contains() function. The advantage of the contains() function over the endsWith() function is that you can use regular expressions with it, which gives a powerful way of specifying the conditions you need the string to match.

The expression:
value.contains(/[.!?]$/)

Will return true if the value in the cell ends with a period, exclamation or questionmark. The way this works is that /[.!?]$/ is a regulary expression which breaks down as follows:

  • The / characters just show the start/end of the regular expression
  • The [.!?] part says "any of the characters in the square brackets" (i.e. period, exclamation, question)
  • $ indicates "end of the string"

So in total this regular expression is testing whether the string contains a period, exclamation or questionmark immediately followed by the end of the string - i.e. the string ends with one of those characters.

Now you have a way of testing whether the string ends with one of these characters, you can use it in a variety of ways - for example you could create a custom text facet with the GREL

value.contains(/[.!?]$/)

and then use that to select the lines that return false - then run the appropriate transformation on those lines.

Alternatively if you want to do the transformation in a single GREL expression you can use:

if(value.contains(/[.!?]$/),value,value+".")

That uses the if control which allows you to do a true/false test and then do different things depending on the outcome

1 Like
6

This is much easier... just use the chomp() function

value.chomp(".")
will remove a period from the end of the string if it ends in a period, but otherwise does nothing

1 Like
7

Thank you so much for your continuous help! I learn a lot from your responses (beyond how to accomplish the desired task) and I really appreciate it.