How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1402
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

So lets say I have these 3 descriptions in column A: A1:

Resolved Question:

So lets say I have these 3 descriptions in column A:

A1: Leather Jacket, Large, Blue, Made in Italy.
A2: Leather Jacket, Red, Small, Made in Canada.
A3: Leather Jacket, Made in China, Black.

So now what I need (and know this is a little complicated unless I'm missing something) is another column, (column X we can call it) that read the sentence and return the word (in this example it would be a color) to column X. I should be able to drag this formula down the entire list and have it correctly give me the color for each item.

So it would end up like this:

X1. Blue
X2. Red
X3. Black


So lets I have a list of 5 possible colors. It can find that word in the sentence and return that word.

I ultimately need to do this for much longer descriptions but this info should allow me to figure out the rest
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 1 year ago.

The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

The-PC-Guy :

is it always going to be in the same order

The-PC-Guy :

for example is the color always going to be 2nd

The-PC-Guy :

?

Customer:

No it wont be in the same order.

Customer:

I can get it to find and return a single word but I cant make it return one of many words.

Customer:

My excel formulas are usually many columns wide

Customer:

I need something that does. Find blue, return blue, if not blue find red, return red, if not red, find black, etc

The-PC-Guy :

it may be possible to do with a macro

The-PC-Guy :

if you create a list of words in another sheet

The-PC-Guy :

then it may be possible to iterate through that list to see if the value is within the big sentance

The-PC-Guy :

but I don't know of any formula that will do this

Customer:

Ill show you what I have so far

The-PC-Guy :

do you mind using vba macro?

Customer:

=FIND("Automatic",A1)

The-PC-Guy :

yup

The-PC-Guy :

the find function will find a single word

Customer:

next column =C1+8

The-PC-Guy :

however it wont iterate through an entire list of words

The-PC-Guy :

you could use nested ifs

Customer:

=LEFT(A1,D1)

The-PC-Guy :

but if you had more than 5 possible words

The-PC-Guy :

that would get kinda crazy

Customer:

=RIGHT(E1,9)

Customer:

I tried that but I got #value

The-PC-Guy :

are you saying you would have way more than 5 possible words?

The-PC-Guy :

to search for

Customer:

Not really

Customer:

maybe 10 at most for some columns

Customer:

so after I did it for "automatic"

Customer:

I do it for "manual wind" and then "quartz"

The-PC-Guy :

its possible to do something like

Customer:

And if the word is in the sentence it shows up at "automatic" but the rest are just #value....what I wanted to do wast assign each possible outcome a number value if and if the other ones are not in the sentence those results would end be 0. then I would add all the columns together

Customer:

So 1 is Automatic 2 is Manual Wind and three is Quartz

The-PC-Guy :

can you send your actual sheet to me

The-PC-Guy :

i think it may be possible as long as you don't have too many words to search from

Customer:


















































Audemars Piguet Royal Oak Offshore Forged Carbon 44 BNIB. Audemars Piguet, Royal Oak Offshore, Forged Carbon, 44mm, BRAND NEW WITH COMPLETE BOX AND PAPERS, Factory Sealed, Factory Warranty, 26400AU.OO.A002CA.01, $36500 List Price, H Series, Skeleton Back, Automatic, Chronograph, Rubber Strap, Swiss Made..257265Audemars Piguet Royal Oak Offshore Forged Carbon 44 BNIB. Audemars Piguet, Royal Oak Offshore, Forged Carbon, 44mm, BRAND NEW WITH COMPLETE BOX AND PAPERS, Factory Sealed, Factory Warranty, 26400AU.OO.A002CA.01, $36500 List Price, H Series, Skeleton Back, AutomaticAutomatic#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
Audemars Piguet Royal Oak Offshore Forged Carbon 44 BNIB. Audemars Piguet, Royal Oak Offshore, Forged Carbon, 44mm, BRAND NEW WITH COMPLETE BOX AND PAPERS, Factory Sealed, Factory Warranty, 26400AU.OO.A002CA.01, $36500 List Price, H Series, Skeleton Back, Manual Wind, Chronograph, Rubber Strap, Swiss Made..257267Audemars Piguet Royal Oak Offshore Forged Carbon 44 BNIB. Audemars Piguet, Royal Oak Offshore, Forged Carbon, 44mm, BRAND NEW WITH COMPLETE BOX AND PAPERS, Factory Sealed, Factory Warranty, 26400AU.OO.A002CA.01, $36500 List Price, H Series, Skeleton Back, Manual WindManual Wind257267Audemars Piguet Royal Oak Offshore Forged Carbon 44 BNIB. Audemars Piguet, Royal Oak Offshore, Forged Carbon, 44mm, BRAND NEW WITH COMPLETE BOX AND PAPERS, Factory Sealed, Factory Warranty, 26400AU.OO.A002CA.01, $36500 List Price, H Series, Skeleton Back, Manual WindManual Wind#VALUE!#VALUE!#VALUE!#VALUE!
Audemars Piguet Royal Oak Offshore Forged Carbon 44 BNIB. Audemars Piguet, Royal Oak Offshore, Forged Carbon, 44mm, BRAND NEW WITH COMPLETE BOX AND PAPERS, Factory Sealed, Factory Warranty, 26400AU.OO.A002CA.01, $36500 List Price, H Series, Skeleton Back, Quartz, Chronograph, Rubber Strap, Swiss Made..257262Audemars Piguet Royal Oak Offshore Forged Carbon 44 BNIB. Audemars Piguet, Royal Oak Offshore, Forged Carbon, 44mm, BRAND NEW WITH COMPLETE BOX AND PAPERS, Factory Sealed, Factory Warranty, 26400AU.OO.A002CA.01, $36500 List Price, H Series, Skeleton Back, QuartzQuartz#VALUE!#VALUE!#VALUE!#VALUE!257262Audemars Piguet Royal Oak Offshore Forged Carbon 44 BNIB. Audemars Piguet, Royal Oak Offshore, Forged Carbon, 44mm, BRAND NEW WITH COMPLETE BOX AND PAPERS, Factory Sealed, Factory Warranty, 26400AU.OO.A002CA.01, $36500 List Price, H Series, Skeleton Back, QuartzQuartz
The-PC-Guy :

can be done without macro in that case

The-PC-Guy :

yea

The-PC-Guy :

I would need the actual xls file

Customer:

Ill send the basic idea

The-PC-Guy :

also which version of office are you using? 2007 or later?

Customer:

Later

Customer:

I think

Customer:

7 maybe

Customer:

window 7

The-PC-Guy :

your version of office

The-PC-Guy :

is not 2003? or earilier

The-PC-Guy :

well you said your formulas are long

The-PC-Guy :

beacuse earlier versions crap out at 7 levels of formula depth

Customer:

I think i have a newer version, I bought my computer 2 years ago

The-PC-Guy :

www.wikisend.com

The-PC-Guy :

if you goto that site

The-PC-Guy :

you can upload the file there

The-PC-Guy :

then provide me the download link they give you

Customer:

ok

Customer:

http://wikisend.com/download/933112/TESTING CHRONO24 COMPATABILTIY.xlsx

Customer:

Ok now you have to go to the sheet that say "MOVT" thats where im trying to figure out the formula

Customer:

On the first sheet, I was filling out the orange part one at a time and it take forever when I have 300 items. I need to be able to just put the decriptions in and drag down the formula for things like Movement and case material

Customer:

Once I now how to make the formula I can tweak it

The-PC-Guy :

so are you looking to know where the word starts

The-PC-Guy :

or do you just want to know if the word exists in the sentance or not?

Customer:

The second one would probably be more helpful. but then I need it to do it for each word for each row, but only one possible phrase or word from the possibilities would be in each row.

The-PC-Guy :

ok

The-PC-Guy :

so on the sheet 1

The-PC-Guy :

column e

The-PC-Guy :

this is where you want this formula?

Customer:

no I will copy it myself, I just need it to create the list in the proper order

Customer:

I dont want to mess with that orange page at all

The-PC-Guy :

so where is the list of words you want to lookup from

The-PC-Guy :

or do we need to create one?

The-PC-Guy :

in other words

The-PC-Guy :

all of the possible values

Customer:

Automatic, Manual Wind, Quartz. is fine for now, I can expand the formula and put in all the possible info later I just need the thing to work

The-PC-Guy :

ok

The-PC-Guy :

give me a few minutes to work out a formula with those 3 values for now

Customer:

thanks

The-PC-Guy :

and the result will go in the F column

The-PC-Guy :

of MOVT sheet?

Customer:

no

Customer:

oh let me look

The-PC-Guy :

put the result in f column , but look for the word in the a column?

Customer:

The F column is acutally just a test, it finds each word I need because I made the first row Automatic, second manual, and third quartz, then I moved the 4 cells that make the answer to the 1 row. so it acutally does it three times but as you can see it returns #value if the word is not in the sentance. I was trying to make it so I could asign a number value to each word and zero if it did not show up. the just add the three numbers and change it back to the word

Customer:

it could go in any column just has to work

Customer:

I may have made it more complicated

The-PC-Guy :

doesn't need to be complicated

The-PC-Guy :

i just need to know where you want the result

The-PC-Guy :

i will have the result be the word itself

The-PC-Guy :

its easier that way

Customer:

Yes that would work. One column with the correct answer that I can copy to the orange page after i put my full list in

The-PC-Guy :

yes, you could just rework the formula

Customer:

ok

Customer:

How long do you think you need do I have time to run outside for a 2 minutes?

The-PC-Guy :

yea

The-PC-Guy :

ill need about 10 - 15 mins

Customer:

ok

Customer:

im back

The-PC-Guy :

still working looooollolol

Customer:

yeah man, its tough

The-PC-Guy :

thats why you seek an expert

The-PC-Guy :

i am done

The-PC-Guy :

uploading now

The-PC-Guy :

hopefully you will be able to make sense of the formula

The-PC-Guy :

PLEASE REMEMBER TO RATE SO I GET COMPENSATED FOR MY TIME

Customer:

Ok

Customer:

im trying to open it but im getting an error for some reason

Customer:

excel cannot open because not enough available resources

The-PC-Guy :

really

The-PC-Guy :

odd sometimes wikisend corrupts files

The-PC-Guy :

let me try a different site

Customer:

ive been having this problem today, my brother put some excel in the dropbox and it did the same thing

The-PC-Guy :

your original opened just fine

Customer:

I opened it, it seems to work, im going to test it now

Customer:

Works great thank you

The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1402
Experience: 20 years experience providing remote computer support
The-PC-Guy and 2 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
The-PC-Guy
The-PC-Guy
327 Satisfied Customers
20 years experience providing remote computer support