+ Reply to Thread
Results 1 to 14 of 14

Formula to Remove 2nd Number Group From String

  1. #1
    Registered User
    Join Date
    10-01-2007
    Posts
    49

    Formula to Remove 2nd Number Group From String

    I've got a string that looks like the following:

    ABC DEF GHIJ 01234567 ABC DEF ABCD 1/02/2011

    I am trying to extract the 1234567 from this string and I'm having some trouble with the mid formula. Many of the strings in the column are different lengths, so I need the number of characters to be higher, like 80, but in the case of this string, 80 characters finds a number, then a date, and returns #VALUE. I need a macro that will find and replace all dates with "", if possible. Or maybe it would be better to remove the second string of numbers in the string, if that's possible, since I believe some of the strings will have 01234567 and then further down the string might have 56789. Any ideas?

    Here is the mid function I'm using:

    {=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))}
    Last edited by Leith Ross; 04-22-2011 at 12:55 AM. Reason: Added more detail

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Formula to Remove 2nd Number Group From String

    Hello chbrandt,

    I have changed your title slightly and moved your post to the correct forum.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula to Remove 2nd Number Group From String

    Not entirely sure I follow... perhaps:

    Please Login or Register  to view this content.
    where A1 holds the string ... it's also not clear if all values will commence with a leading zero or not and if so whether or not you wish to preserve ?

  4. #4
    Registered User
    Join Date
    10-01-2007
    Posts
    49

    Re: Formula to Remove 2nd Number Group From String

    Thanks Donkey, that works for most of my lines.

    About 10% of them have the date show up first, where I need to read only the string of numbers, not the date format. Is there a way to do this? The line may read something like:

    ABC 4/11/2010 CDE FGHI 0123456789 BLAH BLAH

    Thanks!

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula to Remove 2nd Number Group From String

    Do the numbers always start with 0

    Are the numbers of interest always of a given length ?

  6. #6
    Registered User
    Join Date
    10-01-2007
    Posts
    49

    Re: Formula to Remove 2nd Number Group From String

    No and no...that would make things too easy

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula to Remove 2nd Number Group From String

    OK... is the number ever <= 4 digits in length ?

  8. #8
    Registered User
    Join Date
    10-01-2007
    Posts
    49

    Re: Formula to Remove 2nd Number Group From String

    Nope..always 4 or more

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula to Remove 2nd Number Group From String

    So it can be 4 digits ?

    If it's always 5 or more then one possibility:

    Please Login or Register  to view this content.
    where A1 holds string (as before)... note array entry requirement

    I am sure there are better methods... if you were to use a User Defined Function things would be a little simpler.

  10. #10
    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 Remove 2nd Number Group From String

    Try this regular formula:
    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

  11. #11
    Registered User
    Join Date
    10-01-2007
    Posts
    49

    Re: Formula to Remove 2nd Number Group From String

    There will be very few that are 4 digits, I can do those manually I think. This seems to work great, thanks so much. I thought about putting all of the dates in two formats on a hidden sheet and running a macro to do a find/replace on all of those dates, but I'm thinking that won't be worth it if all of the 5+ characters will work.

    Could you basically tell me what this formula is doing, just out of curiosity, to get those 5 or more characters out of the string? Thanks again.

  12. #12
    Registered User
    Join Date
    10-01-2007
    Posts
    49

    Re: Formula to Remove 2nd Number Group From String

    Ron,

    That code works very, very well on lines with dates in them, but if there is no date in the line, which is very common, it gives NA. I'm trying to make an IF for that, but I'm having trouble, as I don't understand some of this formula.

  13. #13
    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 Remove 2nd Number Group From String

    I suspected that might be your situation...
    try this regular formula:
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    10-01-2007
    Posts
    49

    Re: Formula to Remove 2nd Number Group From String

    Ron--

    Sorry to respond to this so much later. One other problem I ran into is what if the date comes BEFORE the number I want to pull in the string, how can I avoid it pulling the 5 digit number from the date? The formula does this well if the date format has two digits for the /DD/ but if you put 4/1/2011 or 4.1.2011 it pulls the date or says N/A, respectively.

    Thanks in advance.
    Chris

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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