+ Reply to Thread
Results 1 to 2 of 2

Delete specific suffixes from a list of company names

  1. #1
    Registered User
    Join Date
    09-19-2014
    Location
    London, ENgland
    MS-Off Ver
    8
    Posts
    1

    Delete specific suffixes from a list of company names

    Hi there,

    I have a *huge* list of company names and want to delete specific suffixes like 'AG', 'SA', 'Corp' etc. Naturally Ctrl + F will not suffice, nor trimming the end of cells.

    I am looking for an Excel formula that will be able to target these words specifically (they are the last words in every cell) and delete them. Any pointers on this would be massively appreciated as it's been driving me nuts.

    Thank you!!
    Last edited by bsmizzle; 09-19-2014 at 07:48 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Delete specific suffixes from a list of company names

    =substitute(a1,"AG","")
    That will replace every instance of "AG" with blank

    Adjust as needed, but fair warning, if a cell contains "Sand" and you use =substitute(a1,"SA",""), you will be left with "nd".

    Use
    =substitute(a1," AG ","") (spaces on either side of "AG") if the letters have a space before and after them, and it won't delete the letter combinations from inside words.

    Or if it's the last word in a sentence, use

    =substitute(a1," AG","") (space before the AG)
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 08-28-2013, 11:02 AM
  2. Making a list of company names
    By juddykong in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-11-2013, 11:10 AM
  3. Extracting Last Names and ignoring suffixes
    By scottintexas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-30-2009, 05:08 PM
  4. Replies: 5
    Last Post: 04-23-2009, 01:48 PM
  5. [SOLVED] Look up Webpage addresses from a List of company names in Excell?
    By jth in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-29-2006, 08:35 PM

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