+ Reply to Thread
Results 1 to 12 of 12

Formula to delete part of the text

  1. 12-17-2015, 05:07 PM #1
    thlee1122
    thlee1122 is offline
    Registered User
    Join Date
    08-06-2015
    Location
    United States, New Jersey
    MS-Off Ver
    2010
    Posts
    10

    Formula to delete part of the text

    Hi all,

    Could anyone tell me a formula that deletes part of the text?

    Below is the texts I have in my file.

    What I want to do is delete those "dash numbers" (ex. -1, -2, -that are placed at the end of the text.

    Could you anyone tell me a formula that does this?

    2015-HE-IRE-LC-44360 (09)-1
    2015-HE-IRE-LC-44359 (09)-2
    2015-HE-IRE-LC-44367 (09)-2
    2015-HE-IRE-LC-44372 (09)-2
    2015-HE-IRE-LC-44376 (09)-2
    2015-HE-IRE-LC-44360 (09)-3
    2015-HE-IRE-LC-44360 (09)-3
    2015-HE-IRE-LC-44362 (09)-3
    2015-HE-IRE-LC-44362 (09)-3
    2015-HE-IRE-LC-44362 (09)-3
    2015-HE-IRE-LC-44372 (09)-3
    2015-HE-IRE-LC-44379 (09)-3
    2015-HE-IRE-LC-44360 (09)-4
    2015-HE-IRE-LC-44367 (09)-4




    Thank you.
    Reply With QuoteRegister To Reply
  2. 12-17-2015, 05:10 PM #2
    FDibbins
    FDibbins is offline
    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
    53,049

    Re: Formula to delete part of the text

    If your required string always ends with )...
    =LEFT(A1,FIND(")",A1,1))
    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
    Reply With QuoteRegister To Reply
  3. 12-17-2015, 05:11 PM #3
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Formula to delete part of the text

    With your sample data in A1:A14

    As long as the only right parenthesis is immediately before the final dash...Try something like this, copied down:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules
    Reply With QuoteRegister To Reply
  4. 12-17-2015, 05:13 PM #4
    FDibbins
    FDibbins is offline
    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
    53,049

    Re: Formula to delete part of the text

    If there will not always be ) at the end...
    =LEFT(A1,FIND("@",SUBSTITUTE(A1,"-","@",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))),1)-1)
    Reply With QuoteRegister To Reply
  5. 12-17-2015, 05:51 PM #5
    thlee1122
    thlee1122 is offline
    Registered User
    Join Date
    08-06-2015
    Location
    United States, New Jersey
    MS-Off Ver
    2010
    Posts
    10

    Re: Formula to delete part of the text

    Thank you very much for your reply.
    It seems like it shows what I wanted.
    My actual data is mixed up of those that ends with ) and ].

    Could you please tell me a formula that would satisfy this situation?

    2014-HE-IRE-LC-94661 (NOV)-3
    2014-HE-IRE-LC-94661 (NOV)-3
    2014-HE-IRE-LC-94648 (NOV)-4
    2015-HE-IRE-LC-46884 (10)-(15-131)-[201510]-1
    2015-HE-IRE-LC-46884 (10)-(15-131)-[201510]-1
    2015-HE-IRE-LC-46884 (10)-(15-131)-[201510]-1

    Once again, thank you very much for your help!
    Hope to hear from you soon.
    Reply With QuoteRegister To Reply
  6. 12-17-2015, 06:26 PM #6
    FDibbins
    FDibbins is offline
    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
    53,049

    Re: Formula to delete part of the text

    will the number alwas be less than 10?
    Did you see my 2nd post (#4)?
    Reply With QuoteRegister To Reply
  7. 12-17-2015, 07:08 PM #7
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.96 for Mac MS 365
    Posts
    8,693

    Re: Formula to delete part of the text

    How about this? =LEFT(A1,(LEN(A1)-2))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  8. 12-17-2015, 07:43 PM #8
    FDibbins
    FDibbins is offline
    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
    53,049

    Re: Formula to delete part of the text

    @ Sambo, that was the reason I asked if the number will be less than 10. If the last part is -10, that wont work
    Reply With QuoteRegister To Reply
  9. 12-18-2015, 09:17 AM #9
    thlee1122
    thlee1122 is offline
    Registered User
    Join Date
    08-06-2015
    Location
    United States, New Jersey
    MS-Off Ver
    2010
    Posts
    10

    Re: Formula to delete part of the text

    The number will be less than 20 for sure.
    I saw your 2nd post.
    When I use the formula that you posted in the 2nd post, it gives me "2014-HE-IRE-LC" instead of "2014-HE-IRE-LC-94661 (NOV)".
    Could you please help me with this?
    Thank you.
    Reply With QuoteRegister To Reply
  10. 12-18-2015, 09:29 AM #10
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,905

    Re: Formula to delete part of the text

    Try

    =LEFT(A1,FIND("*",SUBSTITUTE(A1,"-","*",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1)
    Reply With QuoteRegister To Reply
  11. 12-18-2015, 02:08 PM #11
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.96 for Mac MS 365
    Posts
    8,693

    Re: Formula to delete part of the text

    so, here is my alternative to address Ford's question and your post # 9 if it has -2 or -20 at the end...
    =IF(LEFT(RIGHT(A1,2),1)="-",LEFT(A1,(LEN(A1)-2)),LEFT(A1,(LEN(A1)-3)))
    Reply With QuoteRegister To Reply
  12. 12-18-2015, 06:10 PM #12
    FDibbins
    FDibbins is offline
    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
    53,049

    Re: Formula to delete part of the text

    Quote Originally Posted by thlee1122 View Post
    The number will be less than 20 for sure.
    I saw your 2nd post.
    When I use the formula that you posted in the 2nd post, it gives me "2014-HE-IRE-LC" instead of "2014-HE-IRE-LC-94661 (NOV)".
    Could you please help me with this?
    Thank you.
    Perhaps you entered it wrong?
    A
    B
    13
    		 2015-HE-IRE-LC-44360 (09)-4 2015-HE-IRE-LC-44360 (09)
    14
    		 2015-HE-IRE-LC-44367 (09)-4 2015-HE-IRE-LC-44367 (09)
    15
    		 2014-HE-IRE-LC-94661 (NOV)-3 2014-HE-IRE-LC-94661 (NOV)
    16
    		 2014-HE-IRE-LC-94661 (NOV)-3 2014-HE-IRE-LC-94661 (NOV)
    17
    		 2014-HE-IRE-LC-94648 (NOV)-4 2014-HE-IRE-LC-94648 (NOV)
    18
    		 2015-HE-IRE-LC-46884 (10)-(15-131)-[201510]-1 2015-HE-IRE-LC-46884 (10)-(15-131)-[201510]
    19
    		 2015-HE-IRE-LC-46884 (10)-(15-131)-[201510]-1 2015-HE-IRE-LC-46884 (10)-(15-131)-[201510]
    20
    		 2015-HE-IRE-LC-46884 (10)-(15-131)-[201510]-1 2015-HE-IRE-LC-46884 (10)-(15-131)-[201510]

    B13=LEFT(A13,FIND("@",SUBSTITUTE(A13,"-","@",LEN(A13)-LEN(SUBSTITUTE(A13,"-",""))),1)-1)
    copied down

    All 3 suggestions (John's, Sambo's latesr, and mine) all give the exact same answers
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA to delete part of text string
    By Chrisbrough88 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-15-2014, 06:58 AM
  2. [SOLVED] Delete a row if a cell contains certain text as part of a text string
    By Steve2107 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-15-2014, 07:49 PM
  3. Delete part of a text line with excel?
    By wizi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-29-2014, 04:22 AM
  4. [SOLVED] Delete part of a text in cells.
    By Jeffy14 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-15-2012, 02:36 PM
  5. How do I delete part of the text in a cell?
    By daveofgv in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-27-2011, 10:59 PM
  6. delete middle part of text string
    By maacmaac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2009, 03:59 PM
  7. [SOLVED] Use a formula to delete part of a text string?
    By Josh Craig in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-29-2006, 03:25 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1