+ Reply to Thread
Results 1 to 7 of 7

Formula =Left and =Right Help

  1. #1
    Registered User
    Join Date
    03-03-2021
    Location
    Perth, Western Australia
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO
    Posts
    4

    Formula =Left and =Right Help

    Hello,

    This is my first post here.

    Hopefully the problem I'm having has a fairly simple solution !

    Essentially we currently use the formula below to return the required text:

    =LEFT(H2,FIND(":",H2)-1)

    Cell H2 Contains: 165-3935: BOLT-HEX HEA

    Text returned: 165-3935

    However, due to a change in what our cells now contain the format is now:

    Cell H2 now Contains: STRAP,CAT,261-6797

    Desired Text to return: 261-6797

    I am just unsure of the correct formula to return the desired text in the most simple way.
    Please help and many thanks in advance !
    smc4312

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,542

    Re: Formula =Left and =Right Help

    how consistent are the locations? if it is always 8 spaces in from the right a simple right will accomplish that.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,542

    Re: Formula =Left and =Right Help

    maybe this... =RIGHT(H2,SEARCH(",",H2)+2)

  4. #4
    Registered User
    Join Date
    03-03-2021
    Location
    Perth, Western Australia
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO
    Posts
    4

    Re: Formula =Left and =Right Help

    Quote Originally Posted by Sam Capricci View Post
    how consistent are the locations? if it is always 8 spaces in from the right a simple right will accomplish that.
    Unfortunately the desired text is not always the same length. For example - 1W-2644 or 574-4543 as below.

    SUPPORT ASSEMBLY,CAT,1W-2644
    LIFTER ASSEMBLY,VA,CAT,574-4543

  5. #5
    Registered User
    Join Date
    03-03-2021
    Location
    Perth, Western Australia
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO
    Posts
    4

    Re: Formula =Left and =Right Help

    Quote Originally Posted by Sam Capricci View Post
    maybe this... =RIGHT(H2,SEARCH(",",H2)+2)
    Thanks, but unfortunately i received an error. I will try attaching the excel sheet to this forum post.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-11-2021
    Location
    CANADA
    MS-Off Ver
    Excel 2007, Office 2019
    Posts
    31

    Re: Formula =Left and =Right Help

    Maybe this:

    =TRIM(RIGHT(SUBSTITUTE(H2,",",REPT(" ",99)),99))
    Last edited by Adtxc; 03-03-2021 at 10:47 PM.

  7. #7
    Registered User
    Join Date
    03-03-2021
    Location
    Perth, Western Australia
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO
    Posts
    4

    Re: Formula =Left and =Right Help

    Quote Originally Posted by Adtxc View Post
    Maybe this:

    =TRIM(RIGHT(SUBSTITUTE(H2,",",REPT(" ",99)),99))
    Wow, thanks so much that worked perfectly.

+ 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. Formula to move data to Left when LEFT cell is blank?
    By wer5150 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2019, 05:26 AM
  2. Formula to move data to Left when LEFT cell is blank?
    By wer5150 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-31-2019, 05:22 AM
  3. Replies: 3
    Last Post: 01-10-2019, 09:34 AM
  4. Replies: 6
    Last Post: 06-26-2017, 09:04 PM
  5. [SOLVED] Using Countif formula on cells that have a Left formula
    By fsoares22 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-10-2013, 11:13 AM
  6. Formula help for IF, OR, LEN(), LEFT()
    By winstontj in forum Excel General
    Replies: 5
    Last Post: 09-06-2009, 02:58 AM

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