?
Solved

formula to extract data to left of words in cell

Posted on 2012-09-19
5
Medium Priority
?
269 Views
Last Modified: 2012-09-19
excel 2010:

I have Column O

I this column I have cells that have the word  "best buy"

"STEREO HEADSET BEST BUY #22323
"SOFTWARE BEST BUY #44345"

i NEED TO EXTRACT ANYTHING TO THE RIGHT OF THE WORD "BEST BUY" please


Thanks
fordtaiders
0
Comment
Question by:Fordraiders
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Shanan212
ID: 38415565
=MID(O1,FIND("BEST BUY",O1,1)+8,LEN(O1))

Considering you start from O1 (this would have a space infront since after BEST BUY is a space)

Alternatively,

If you wnat everything after BEST BUY(SPACE)

=MID(O1,FIND("BEST BUY",O1,1)+9,LEN(O1))
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 38415589
shanan,
Only give me 6 characters after the word best buy...instaed of everything to the right ?
if possible...

Thanks
fordraiders
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38416031
Can you give the example of the text for which you only get 6 characters? Shanan212's formula should work no matter what.

Thomas
0
 
LVL 13

Accepted Solution

by:
Shanan212 earned 2000 total points
ID: 38416251
=MID(A1,FIND("BEST BUY",A1,1)+9,6)

If you only want the 6 chars after the word, then use above. If you want more chars, change the bold number as you wish
0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 38416489
Thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question