If cell contains value plus other random stuff, then

[Alba_Machado2]

Hustling bookseller’s back. So I inherited thousands of books from my dad, and he, erm, didn’t have a lot of foresight when it came to the way keywords would be used in searches – especially not on Amazon, which has a standard set of keyword values that are permitted to booksellers. What I want to do is create a formula field that looks at Dad Keyword “AMERICANA #7833” and renders only AMERICANA, leaving off the useful-only-to-Dad random characters. Sometime he’d stick in letters, punctuation – I have no idea what he was thinking (but bless him, he knew how to find good used books). What kind of formula might work here? Thanks in advance to anyone who takes a crack at this!

[kuovonne]

Although your Dad didn’t have the foresight to use Amazon keywords, how wonderful that he cataloged the books in electronic format.

Do the “Dad Keywords” follow a pattern? For example, do always want the words that are before the # symbol? Or do you need to take into account some other ways he might have formatted keywords?How many keywords are in the standard set of keywords from Amazon? Do you need a one-time conversion for all records, or will you need to extract keywords on an ongoing basis?

For the one example you gave of AMERICANA#7833 you can use the following formula.

IF(
  FIND("#",{Dad Keyword}),
  LEFT(
    {Dad Keyword}, 
    FIND("#", {Dad Keyword}) - 1
  ),
  {Dad Keyword}
)

You can see a demo on my website.

[Alba_Machado2]

Oh wow, Kuovonne. I really didn’t think I’d get a solution for this one. And I wouldn’t have if you hadn’t stopped to think of those patterns. Unfortunately, he alternated between putting the keyword to the left and the right of the random string of characters that always started with #, so this will only help with a chunk of the records – but still. Thank you. That’s time I definitely need right now. :green_heart:

[Alba_Machado2]

Kuovonne, I checked out your website. I can’t really afford to hire a consultant right now, but I’m wondering if you have lesson videos on Skillshare, YouTube, or elsewhere…?

[kuovonne]

@Alba_Machado2

Glad I could help.

I do not have video based lessons. Several other consultants do, and I decided that instead of competing with that, I would provide the more text based instruction that you see on my website. (Plus, that’s how I like to learn.) I do plan on adding more content as I have time.

Don’t worry about not having funds to hire a consultant right now. However, if you ever decide that you’d rather spend the money instead of your time, let me know. While other consultants prefer only large projects, I’m okay with smaller assignments that focus on building out just the trickier bits.