Results 1 to 6 of 6

Thread: Extracting alpha-numeric numbers while leaving numeric numbers

  1. #1

    Extracting alpha-numeric numbers while leaving numeric numbers



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    Could some one please help me with the following?


    I have a column A with alpha and numeric numbers as:

    A1

    12345-p
    23456
    23452-P
    45677

    Results in B1 should appear like this, removing only the alpha numeric numbers.

    B1


    23456

    45677

    I appreciate your help and time. Sample attached.


    Guru-question.xlsx

  2. #2
    Something like this?
    Attached Files Attached Files

  3. #3
    Thank you first of all Rizky, but the numbers that have the "-P" I needed the cell to be blank as in this image and on the worksheet I had attached. Sorry for not being clear.
    Click image for larger version. 

Name:	dash-p.jpg 
Views:	7 
Size:	38.6 KB 
ID:	3225

  4. #4
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    Try below formula

    =IF(ISERR(VALUE(MID(A3,7,1))),"",A3)

    pl find attachment
    Attached Files Attached Files

  5. #5
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    you can also try below formula


    =IF(ISERR(VALUE(MID(A1,SEARCH("-",A1)+1,1))),"",A1)

  6. #6
    Quote Originally Posted by sambit View Post
    Hi,
    you can also try below formula


    =IF(ISERR(VALUE(MID(A1,SEARCH("-",A1)+1,1))),"",A1)

    Thank you Sambit for both formulas.

    I will test further but initial tests show that the formula does remove the "-P" in the alphanumeric number say 16450-P, and will not paste it into cell B2, which is great, but if one removes say the "-P" from the number "16450-P" in cell say A1, the remainder "16450" will not be transferred to cell B1 but instead be left blank.
    One would need to place either a "-0" trailing the number such as "16450-0" or 16450-1P" then it will transfer that number from A1 to cell B2.

    This request is tricky I know, but you have provided me with some great information here as always. Thanks again for your time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •