Custom Formula Fields

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
Leonard PaladAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leonard PaladAuthor 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 ConsultantCommented:
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 SoftwareCommented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Leonard PaladAuthor 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.
Leonard PaladAuthor 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 ConsultantCommented:
DO you have a list of the keywords to look for or how do you know they are keywords.

mlmcc
Leonard PaladAuthor Commented:
it turn out the formula i'm looking is too complex for me; thank you

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike McCrackenSenior ConsultantCommented:
DId you find a solution?
Leonard PaladAuthor Commented:
Hi Mike, no, i end up hiring freelancer in upwork because the complexity of my requirement.

thanks
Mike McCrackenSenior ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.