topic Re: Removing Parts of Value Strings Using Functions/Mapping in App Development

Removing Parts of Value Strings Using Functions/Mapping

Tue, 29 Sep 2015 23:45:08 GMT

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?

Re: Removing Parts of Value Strings Using Functions/Mapping

lironbaram — Wed, 30 Sep 2015 07:30:36 GMT

yes

use this function

left(yourfield,index(yourfield,' ')-1)

Re: Removing Parts of Value Strings Using Functions/Mapping

jonathandienst — Wed, 30 Sep 2015 07:33:10 GMT

The Left() expression above will split on a space. Another way is

=purgechar(yourfield, ' .1234567890')

Re: Removing Parts of Value Strings Using Functions/Mapping

Anonymous — Wed, 30 Sep 2015 07:56:56 GMT

Hello,

Ideally this solves your problem.

subfield([Your Field],' ',1)

Serhan

Re: Removing Parts of Value Strings Using Functions/Mapping

Wed, 30 Sep 2015 16:11:01 GMT

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

Re: Removing Parts of Value Strings Using Functions/Mapping

Wed, 30 Sep 2015 18:18:36 GMT

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

Re: Removing Parts of Value Strings Using Functions/Mapping

Wed, 30 Sep 2015 19:06:50 GMT

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