# Thread: Extracting alpha-numeric numbers while leaving numeric numbers

1. ## Extracting alpha-numeric numbers while leaving numeric numbers

Hi,

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  Reply With Quote

2. Something like this?  Reply With Quote

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.   Reply With Quote

4. Hi,
Try below formula

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

pl find attachment  Reply With Quote

5. Hi,
you can also try below formula

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

6. Originally Posted by sambit 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.  Reply With Quote

#### Posting Permissions

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