Custom Formula Fields

Leonard Palad
Leonard Palad used Ask the Experts™
on
Hi,

Im new to Crystal.

I have an excel file that i'm using as my datasource file.

In the excel file, there is a column I (Customer Search Term)

I want to create a Formula Field to view only the first word of column I.

For example,  the below Customer Search Term, i want to extra only the first word "anysharp".


anysharp pro knife sharpener

But, there are first word or number begin with 20 or 3 in the Customer Search Term (column I), i want those too to be extracted like shown below

7 degree knife sharpener

I want to extract "7" only

7

Hope i have explain my problem above and thanks for your help.

Leonard
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Also, if i want to remove any duplicates in the column I (Customer Search Term), how can i incorporate in the formula the following too:

For example, i have bunch of result shows in the following:
knife
knife
knife
When i look at the  column I (Customer Search Term) in excel, the following data existed:

knife sharpener guide
knife sharpener kit

So, the expanded formula will look at the data and excluded any duplicate words (mostly two words), for example, then splits out the next word after the duplicate.

example
knife sharpener guide
knife sharpener kit

Result,
guide
kit

Hope it make sense ?
thanks
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try this

Split({Yourfield})[1]

You could group on it and show the result in the group header

mlmcc
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
To answer your first question, use a formula like this:
Split({your_Column}, " ")[1]
This splits the content into an array using a single space as the separator.
And returns the 1st element in the array.

Your second question is a bit confusing. Try to start a new thread and clarify the situation.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hello Ido,

Thanks.  How do i refine your formula to ignore keywords that are identical - see this link https://www.screencast.com/t/8KRns222

For example looking at the above link for 'electric knife sharpener' , those keywords are repeated 6x times,
so i want to ignore the repeated words - electric knife sharpener - instead extra the next word after - example from the above link the below output should be

Customer search term
electric knife sharpener 220-24- volts
electric knife sharpener professional
electric knife sharpener reviews
electric knife sharpener scissors

Results should be:
-220-240
-professional
-reviews

how this make sense.

Author

Commented:
Thanks Mike, but i need to refine it as explained above.

i need the unique keyword after any duplicate words

Customer search term
electric knife sharpener 220-24- volts
electric knife sharpener professional
electric knife sharpener reviews
electric knife sharpener scissors

Results should be:
-220-240
-professional
-reviews
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
DO you have a list of the keywords to look for or how do you know they are keywords.

mlmcc
it turn out the formula i'm looking is too complex for me; thank you
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
DId you find a solution?

Author

Commented:
Hi Mike, no, i end up hiring freelancer in upwork because the complexity of my requirement.

thanks
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Ok.  I don't know if it is that complex to solve but I think it would have taken a lot of discussion to get the full requirement.  It is much easier to handle it that way where you can have a long discussion in one session

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial