6 Replies Latest reply: Sep 30, 2015 2:18 PM by Brett Snider RSS

    Removing Parts of Value Strings Using Functions/Mapping

      I have data from a CSV file that has values in a specific field like this:

       

      Apple 9.29.15

      Carrot 9.28.15

      Banana 10.11.14

      Orange 8.28.15

      Apple 9.28.15

      Carrot 9.27.15

       

      Ideally, I would like the values to read like this:

       

      Apple

      Carrot

      Banana

      Orange

      Apple

      Carrot

       

      Since the string values aren't the same character length from the left or the right, I've had a hard time using LEFT or RIGHT functions to snip out these number values.

       

      I also considered using a mapping function to essentially treat values like "Apple 9.29.15" and "Apple 9.28.15" as the same, but it seems to entail actually going through and manually mapping out all instances. This would not be practical with the scale of my data. It also appears that it's not as easy as mapping "Apple" to "Apple*" -- unless there's an easy shortcut to mapping with wildcards.

       

      Does anyone have a suggestion how I can either clean or map my data to appear as above?