+ Reply to Thread
Results 1 to 10 of 10

Help....I want to delete 3 digit numbers from every row without disturbing other data.

  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    Islamabad, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    20

    Help....I want to delete 3 digit numbers from every row without disturbing other data.

    Ok,
    I have data with 8000+ rows.
    The data is combined in on column for each row(entry).
    It contains Last name, first name, dates, account#, dollar amount, etc.
    every thing is just inside one column separated by a sapce

    Now i want to delete specific entries within each row, there are the three or four digit random numbers coming in every row (like 932 799 125 2514 1450 etc).
    If i make the data "text to column" these random numbers are not sorted under one column. Some of these number come under column B, C, D, E depending on the other missing fields in the rows.

    Can I run a formula to delete (3 digits) (4 digits) from every row. so that where it finds a number in row with three digits it simply delete it so that i can get rid of these random number from the rows.
    I cant do it manually because there are 8000+ entries.
    Someone please suggest me a formula to run accordingly for deleting 3digit number from a row, so that I can apply to all rows.

    Hope you understand what I am saying.


    Thanks,

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Help....I want to delete 3 digit numbers from every row without disturbing other data.

    Can you post some sample data and the results you want to see, based on that data?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Help....I want to delete 3 digit numbers from every row without disturbing other data.

    Try this..

    It will remove all 3 digit and 4 digit numbers as long as they are the 'first numbers' to occur in the cell value. Any number less than 3 digits or more than 4 digits will not be deleted.

    =IF(OR(LEN(LOOKUP(99^99,--("0"&MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&1234567890)),ROW(1:10000)))))={3,4}),SUBSTITUTE(B2,LOOKUP(99^99,--("0"&MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&1234567890)),ROW(1:10000)))),""),B2)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    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,917

    Re: Help....I want to delete 3 digit numbers from every row without disturbing other data.

    this may be simpler, assuming your data is in A1 and all you want to do is remove the 1st 3 digits...

    =RIGHT(A1,LEN(A1)-3)
    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

  5. #5
    Registered User
    Join Date
    08-10-2012
    Location
    Islamabad, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Help....I want to delete 3 digit numbers from every row without disturbing other data.

    These are not the first digits in a row. They are somewhere in the middle of data or may be at times comes at the last of row.

    I am attaching a screenshot for better understanding.


    Thanks,
    Attached Images Attached Images

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Help....I want to delete 3 digit numbers from every row without disturbing other data.

    Why post a picture when you obviously have a samplr worksheet?

    Post the workbook!!!

    When you are in Advanced reply, click on the paperclip to open the upload window.

    View Pic

  7. #7
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Help....I want to delete 3 digit numbers from every row without disturbing other data.

    Did you try my suggestion in Post#3? Does it work? Would love some feedback.

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help....I want to delete 3 digit numbers from every row without disturbing other data.

    motivationalist,

    Welcome to the forum!
    Attached is an example workbook based on the criteria you described. In cell B2 and copied down is this formula:
    Please Login or Register  to view this content.
    Does that work for you?
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  9. #9
    Registered User
    Join Date
    08-10-2012
    Location
    Islamabad, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Help....I want to delete 3 digit numbers from every row without disturbing other data.

    Hi Marcol.
    I have attached sample workbook with few entries for review.


    @Ace_XL.... Thanks for your input. Yes I tried your suggestion as well, but it returned with the same entries as before. can you see the attached excel spreadsheet so that you have a better idea on it.
    Attached Files Attached Files

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Help....I want to delete 3 digit numbers from every row without disturbing other data.

    Try this in B2, Drag/Fill Down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Provided you have no 3 character words in the string e.g. " abc " then it should work.

    [EDIT]
    Just noticed
    1107221 2099 TAYLOR KIM 12/01/11 11/22/11 206.00 201 209.99 11/25/11 (In Row 14)
    Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Marcol; 08-13-2012 at 12:46 PM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

+ 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