How to Apply Trim and Title Case to DataTable

[jdahlinger (Julianna Dahlinger)]

Hello,

I am struggling to apply both a Trim and Title Case to a column in my datatable. I am using a For Each Row activity and have successfully trimmed the writespaces out using the following Assign activity:

row(“First Name”) = row(“First Name”).ToString.Trim

However, now I am trying to change the result to Title Case so that I can properly join on another datatable. I was trying to use the following, but I am not sure how to properly use it based on my trimmed data.

CultureInfo.CurrentCulture.TextInfo.ToTitleCase(your_str_var.ToLower())

Can anyone point me in the right direction? Is there any way to combine these steps or do they need to be done separately?

Thank you.

[ppr (Peter Preuss)]

@jdahlinger
give a try on right side from assign:
StrConv(row(First Name").toString.Trim.ToLower(),VbStrConv.ProperCase)

[jdahlinger (Julianna Dahlinger)]

Thank you for the suggestion. I tried that but unfortunately, it ends up excluding the record that was in all caps. See screenshots below. The first is my source table and the second is my result after I applied the code you provided. I have an Assign activity for both the first and last name columns.

[jdahlinger (Julianna Dahlinger)]

I am wondering if this has something to do with trying to get a dynamic read range. I am first reading the whole sheet and then trying to do a count to determine the actual number of rows to read. Could something be wrong with the way I am handling this that would be excluding that final row?

[jdahlinger (Julianna Dahlinger)]

This was my error. I changed my first Read Range to just show “A10” and then removed the second range and it worked perfectly. Still read all the rows and the Assign worked. Thank you @ppr!!!