Extracting alpha-numeric numbers while leaving numeric numbers

[excel1]

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

[Rizky]

Something like this?

[excel1]

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.

[sambit]

Hi,
Try below formula

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

pl find attachment

[sambit]

Hi,
you can also try below formula


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

[excel1]

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.