+ Reply to Thread
Results 1 to 8 of 8

Delete every thing before # sign

  1. #1
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,597

    Delete every thing before # sign

    KD-0219 GIRLS EVENING WITH BCK STRP #1350
    KD-0247 (X11368) GIRL SPORT VELCRO M/MOUSE RS #2950


    I have this on A1 and A2
    Expected results

    KD-0219 #1350
    KD-0247 #2950

    This mean delete after thing before sign leaving the first word

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Delete every thing before # sign

    If the text is in A1:

    =LEFT(A1,8)&RIGHT(A1,LEN(A1)-FIND("#",A1)+1)

    This assumes that the opening code will always be the same length.
    Last edited by AliGW; 10-26-2015 at 06:32 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Valued Forum Contributor mangesh.mehendale's Avatar
    Join Date
    06-04-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    510

    Re: Delete every thing before # sign

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    will work for any string
    Last edited by mangesh.mehendale; 10-26-2015 at 06:19 AM.
    Don`t care, take care...

    Regards,
    Mangesh

  4. #4
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,597

    Re: Delete every thing before # sign

    Tried not works , did it work your side

  5. #5
    Valued Forum Contributor mangesh.mehendale's Avatar
    Join Date
    06-04-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    510

    Re: Delete every thing before # sign

    Yes both are working to my end..


    see there is little change in Ali`s formula its not F8 (which will work only if len of "KD-0219" is 7) and mine will work for any string

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see attached file
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Delete every thing before # sign

    Quote Originally Posted by mangesh.mehendale View Post
    Yes both are working to my end..


    see there is little change in Ali`s formula its not F8 (which will work only if len of "KD-0219" is 7) and mine will work for any string

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see attached file
    It was a typo - now corrected - thanks!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Delete every thing before # sign

    Quote Originally Posted by makinmomb View Post
    Tried not works , did it work your side
    Is this because the data you are trying it on is in a different format to that which you provided in your first post?

  8. #8
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,597

    Re: Delete every thing before # sign

    mangesh your works , thank you very much

+ 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. [SOLVED] If cell contains text do one thing, if it contains number do another thing
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-16-2013, 11:50 AM
  2. [SOLVED] How to delete minus sign in front of numbers in every row?
    By mrsewer in forum Excel General
    Replies: 4
    Last Post: 07-29-2013, 12:33 PM
  3. [SOLVED] How to delete specific numbers and or sign in a column
    By mrsewer in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-25-2013, 03:54 PM
  4. Excel Formula to delete & sign at end of cell
    By shanetedder in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-17-2013, 03:30 PM
  5. delete rows of specific value or sign
    By avk in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-20-2010, 03:57 PM
  6. Need help to delete the 'white' thing
    By souke0000 in forum Excel General
    Replies: 2
    Last Post: 05-15-2009, 01:28 AM
  7. find and delete $ sign from formulas in a spreadsheet
    By nicko54 in forum Excel General
    Replies: 7
    Last Post: 07-25-2008, 09:36 AM
  8. XL invoice replace the dollar sign with euro sign
    By Pamela casares in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-16-2005, 04:25 PM

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