+ Reply to Thread
Results 1 to 6 of 6

Formula to remove smaller text string in cell

  1. #1
    Registered User
    Join Date
    11-13-2012
    Location
    ann arbor
    MS-Off Ver
    Excel 2010
    Posts
    67

    Formula to remove smaller text string in cell

    Hi. I am having trouble working with a list of names, specifically first names that sometimes contain middle initials and other times do not. Here is an example of the data found in the Fname column:

    Mike
    Chris S.
    Tom R
    S
    M
    Roger
    L
    Quin T.

    What I need to do is remove the middle name initials that occur randomly throughout the list but also keep the occasional cell which only has a first name initial (like the: S M and L in the example). Once the formula is ran the example data would look like this:

    Mike
    Chris
    Tom
    S
    M
    Roger
    L
    Quin

    So it removed the smaller text string in the cell but kept initials that were alone. Any thoughts I am having some trouble with this one.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Formula to remove smaller text string in cell

    hi LightingPop. it looks like you want everything before the space, so try:
    =IFERROR(LEFT(A1,FIND(" ",A1)-1),A1)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,972

    Re: Formula to remove smaller text string in cell

    Little more complex that it appears because of the single letter and no singe letter. Try this, copied down...
    =IF(ISERROR(FIND(" ",A1,1)),A1,LEFT(A1,FIND(" ",A1,1)-1))

    edit Ben beat me to it, with a shorter version
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    11-13-2012
    Location
    ann arbor
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Formula to remove smaller text string in cell

    Thank you both so much! This is going to be an enormous help and will save me personally a lot of time. Thanks!

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2407 Win 11 Home 64 Bit
    Posts
    24,068

    Re: Formula to remove smaller text string in cell

    You both beat me, but here is my contribution anyway:

    =IFERROR(LEFT(A1,IFERROR(FIND(" ",A1,1),"")-1),A1)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,972

    Re: Formula to remove smaller text string in cell

    Happy to help and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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