Ok, I hope you guys can help me out. I took a short excel class at college, (more of just a section of my class) and I was wondering something, is there a way to say find the words "the" between x and y, then say what is in the cell to the left or right of those words?
Last question is how do you name a set of data?
There is likely a better solution, but . . .is there a way to say find the words "the" between x and y, then say what is in the cell to the left or right of those words?
Say A1 holds the phrase "see the dog run"
B1 formula: =LEFT(A1,FIND("the",A1,1)-1)
B1 result = See
C1 formula: =TRIM(MID(A1,FIND(" ",A1,FIND("the",A1,1)),LEN(A1)-(FIND("the",A1,1))))
C1 result = dog run
See the link for help on named ranges: Contextures - Named RangesLast question is how do you name a set of data?
With say A1:A4 of:
Another variant might besee the dog run
see dog run
the hills are alive with the sound of music
come to the
Note we're assuming you only split based on the first instance of "the"
B1: =TRIM(LEFT(A1,FIND("the",A1&"the")-1)) C1: =TRIM(REPLACE(A1,1,FIND("the",A1&"the")+2,"")) copied down
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Latszer 
Register To Reply
Bookmarks