+ Reply to Thread
Results 1 to 14 of 14

Formula to remove part of a text from a cell by lookin at a table

  1. #1
    Registered User
    Join Date
    07-27-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Question Formula to remove part of a text from a cell by lookin at a table

    Hi All,

    I've got a small problem that i hope that someone will be able to help with.

    Here goes:

    I have the following text in a cell: CORPO POM BEANIE
    (Corpo Pom is the product name & Beanie is the product)

    I want to be able to remove the word 'BEANIE' from the cell by a formula that will look up the word BEANIE from a table of products in another tab and remove it from the cell leaving me with CORPO POM so that i can have just the product name in the cell.

    I have attacehed a sample of what im trying to do, The product list is 1000's of lines long so you can see why i would like a formula to do this

    Any questions please shout and i will try to help

    I am using Excel 2007

    Many thanks

    Jamie
    Attached Files Attached Files
    Last edited by jamieray; 09-19-2011 at 04:34 PM.

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Formula to remove part of a text from a cell by lookin at a table

    Hmm...?
    Your posted sample workbook appears to be empty ...

  3. #3
    Registered User
    Join Date
    07-27-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Formula to remove part of a text from a cell by lookin at a table

    Opps, not sure how that happend.

    Hopefully its now attached
    Attached Files Attached Files

  4. #4
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Formula to remove part of a text from a cell by lookin at a table

    Without seeing the other tabs, I'm not sure if this is all you need.

    Sounds like all you need to do is a "find and replace" on each term -with only 35 terms would be pretty simple to do w/o anything fancy.

    Just press cntrl+F,
    in the Find What box enter Beanie with a space in front of it (like " Beanie"), leave the Relace With box empty.

    Under options select Within Workbook and press Replace All.

    For that size of list that should do the trick.

  5. #5
    Registered User
    Join Date
    07-27-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Formula to remove part of a text from a cell by lookin at a table

    I had thought of doing that as an option but this is only a sample as the whole list of products that needs to be removed is in the 100's and the actual products with names is a list of 1000's!!

    Thank you for your surgestion though.

    Jamie

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Formula to remove part of a text from a cell by lookin at a table

    How doo we know when a company name ends?
    Is it when the text colour changes to a colour other than black?

    If so try this UDF
    In a standard module.
    Please Login or Register  to view this content.
    Then in E2
    Please Login or Register  to view this content.
    Drag/Fill Down

    You can then copy the results and Paste Special > Values if you need to.
    Its' not bullet proof, but it might help.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  7. #7
    Registered User
    Join Date
    07-27-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Formula to remove part of a text from a cell by lookin at a table

    Hi Marcol,

    I think ive confused matters by making parts of the text red, this was just to show which parts of the cell needed to be deleted from the orinal text and how it could be found in table that will be another tab.

    I have been using the below fro another part of my spreadsheet:

    =IFERROR(INDEX(Table,MATCH(TRUE,INDEX(NOT(ISERROR(SEARCH(Foxtype,B2))),0),0),2),"Missing from table")

    I have a string of text in cell B2 'Concept Shoe BLACK/GREEN 8' (its a stock list) and lots of other random produt names in column B, what i need to do is get the formula in C2 to look at B2 for a certain word that i have in a table (foxtable - Fox is the clothing brand and i have a list in there of the key words to search i.e Shoe, Jean, Pant, Tee, Knit, Polo ect this is on a seperate tab) and then return a word that is in the next column of that table (Table is the combination of Foxtable on another tab in colum A with what i need returned in Column B)

    And i need something similar that just takes away a bit of text and leaves everything else and that word would be looked up from a table.

    Im afraid that VBA is a bit beyond me at the moment and im only just starting to touch on the basic.

    Many thanks

    Jamie

  8. #8
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: Formula to remove part of a text from a cell by lookin at a table

    =LEFT(B2,LOOKUP(9.99999999E+307,SEARCH($B$29:$B$63,B2,1))-1) this will do for most of your list. I say most, because some of the words in the Product table have a different spelling to the word requiring to be referenced from the Product name and Products table. For instance EIGHTY SIX BOARDSHORT & Boardshorts the extra "s" in the second list, will that be the same in your real data? or is that just typos from your sample?

    If it's the case there will be words spelled differently as per your sample, you need to incorporate all the possible variations of the words to be removed into the list then reference the whole list in the first part of the Search argument

    If your going to be using the word that your left with in a Vlookup or similar afterwards, then I suggest wrapping in the Trim function to remove the leading space your list has

    TRIM(IFERROR(LEFT(B2,LOOKUP(9.99999999E+307,SEARCH($B$29:$B$63,B2,1))-1),""))
    Last edited by scottylad2; 09-19-2011 at 01:50 PM.
    Windows 7 using Office 2007 & 2010

    Remember your [ code ] [ /code ] tags, makes reading soooo much easier

  9. #9
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Formula to remove part of a text from a cell by lookin at a table

    I see..the 35 products were just an example...you could try using this kind of macro (I've run it on your original sheet and attached the results.

    As you can see, the trick will be getting the list of words to remove

    Please Login or Register  to view this content.

  10. #10
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Formula to remove part of a text from a cell by lookin at a table

    sorry, here is the file (attached) you can see what got left behind.

    you will have to build the list of words to remove to include all product variations...boardshort & boardshorts..etc.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-27-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Formula to remove part of a text from a cell by lookin at a table

    Quote Originally Posted by scottylad2 View Post
    =LEFT(B2,LOOKUP(9.99999999E+307,SEARCH($B$29:$B$63,B2,1))-1) this will do for most of your list. I say most, because some of the words in the Product table have a different spelling to the word requiring to be referenced from the Product name and Products table. For instance EIGHTY SIX BOARDSHORT & Boardshorts the extra "s" in the second list, will that be the same in your real data? or is that just typos from your sample?

    If it's the case there will be words spelled differently as per your sample, you need to incorporate all the possible variations of the words to be removed into the list then reference the whole list in the first part of the Search argument

    If your going to be using the word that your left with in a Vlookup or similar afterwards, then I suggest wrapping in the Trim function to remove the leading space your list has

    TRIM(IFERROR(LEFT(B2,LOOKUP(9.99999999E+307,SEARCH($B$29:$B$63,B2,1))-1),""))
    That works great!!

    Yes i will have to add every possable text variation.

    Thank you

    One last thing, where do i put 'Proper' in the formula to change it to Proper?

  12. #12
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: Formula to remove part of a text from a cell by lookin at a table

    Quote Originally Posted by jamieray View Post
    That works great!!

    One last thing, where do i put 'Proper' in the formula to change it to Proper?
    Do you mean change the case? are you wanting Proper or UPPER Proper will give you a leading capital letter at every word, UPPER will return all the words in Upper case? is that what you mean?

    For Proper use

    =PROPER(TRIM(IFERROR(LEFT(B2,LOOKUP(9.99999999E+307,SEARCH($B$29:$B$63,B2,1))-1),"")))

    To return the entire selection as UPPER case use
    =UPPER(TRIM(IFERROR(LEFT(B2,LOOKUP(9.99999999E+307,SEARCH($B$29:$B$63,B2,1))-1),"")))
    Last edited by scottylad2; 09-19-2011 at 04:20 PM.

  13. #13
    Registered User
    Join Date
    11-05-2011
    Location
    Kuala Lumpur
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Formula to remove part of a text from a cell by lookin at a table

    Hi,

    anyone can help me on this....

    asdffh@usa.comSMTPYA Sddh (asdffh@usa.com)
    qwerty@eeg.po.mySMTPY. q. wert (qwerty@eeg.po.my)

    i have thousands of row to deal with, need to know how to delete the wording from SMTP onwards to make the content of the cell a valid email?

    Please help...Thanks

  14. #14
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Formula to remove part of a text from a cell by lookin at a table

    i Casy

    Welcome to the forum
    Read this Forum Rules before the moderators get you...

    Forum Rules
    2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.
    If you can Edit/Delete your post, do so, if not you'll need to ask a moderator to help you.

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1