1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Macro to truncate up to last number

Discussion in 'Business Applications' started by PincivMa, Feb 26, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi There

    Not sure if this is possible but if it is, it will save me a ton of work and time. Here is my problem. I have many, many rows of numbers. Some of these numbers have a B or an BX or any other letter attached at the end. Here is an example.

    256B
    569875BX
    12589S
    26987498RED
    256-G
    1259

    What I want is a macro that takes away the letters and dashes if any and just leave numbers. So the final result should look like this

    256
    569875
    12589S
    26987498
    256
    1259

    Get the picture??

    Thanks a bunch.

    Mario
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,255
    First Name:
    Wayne
    try this
    Code:
    =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))
    use control+shift+enter to make it an array formula
    
    then you will get { } around
    
    {=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))}
    It will not sort 
    123AB567
    
    only letters before or after
    
    see attached
     

    Attached Files:

  3. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520

    Attached Files:

  4. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi Guys

    Both formulas work. The formulas look very complicated. How do you guys do that??

    Thanks

    Mario
     
  5. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Google! (for me - etaf probably knows what he is doing)
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1091058

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice