Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
yes
use this function
left(yourfield,index(yourfield,' ')-1)
The Left() expression above will split on a space. Another way is
=purgechar(yourfield, ' .1234567890')
Hello,
Ideally this solves your problem.
subfield([Your Field],' ',1)
BR
Serhan
I like the idea of using the purgechar expression to remove the numbers after the value. I don't think splitting based on spaces will work because my data looks more like this:
Banana Fruit Salad 10.14.15
Orange Sherbet 11.22.15
Apple Crisp 11.15.14
Pineapple Upside Down Cake 10.14.14
There are also values that look like this:
2nd Best Pineapple Cake 10.12.14
1st Place Orange Sherbet 10.11.14
So purging number characters is a no go
What would be the best way via function to search the string for the first number value following a space and then deleting everything to the right of that number (including the number)? That way you could have:
Banana Fruit Salad 10.14.15
Orange Sherbet 11.22.15
Apple Crisp 11.15.14
Pineapple Upside Down Cake 10.14.14
2nd Best Pineapple Cake 10.12.14
1st Place Orange Sherbet 10.11.14
Result in:
Banana Fruit Salad
Orange Sherbet
Apple Crisp
Pineapple Upside Down Cake
2nd Best Pineapple Cake
1st Place Orange Sherbet