+ Reply to Thread
Results 1 to 5 of 5

shortening word Length

  1. #1
    Registered User
    Join Date
    09-19-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    48

    shortening word Length

    I have data in col A in the following manner

    A1 ALP_STD_Store_Fuel
    A2 ALP_DEL_Store_FoodGrain
    A3 DRJ_BAS_Company_Fuel
    A4 STD_DTC_Office_Staff
    and so on .......................

    Each word is seperated with an Underscore "_"
    Now I want only the last word (from Right) in each cell to be displayed on the adjucent cells.

    For example
    In Col B1 I want only Fuel, in B2 only FoodGrain, in B3 only Fuel and B4 only Staff.
    Which command or function (without VBA) allows me to do that.

    Thanks for any help you offer.

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: shortening word Length

    Try:

    =TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",50)),50))

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    09-19-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: shortening word Length

    Great work Dom. This is working absolutely fine.
    I was also trying to understand the code about how it works. Please could you also explain why 50 is mentioned. I am just curious.
    Thank you so much for your help
    pal

  4. #4
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: shortening word Length

    50 is the maximum length of the string you want to return. Covers most things but extend it if need be.

    Dom

  5. #5
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: shortening word Length

    You can also view a formula's calculation/process step-by-step using the 'Evaluate Formula' function on the Formulas tab.

+ 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