Parse Numerics

[MWilliams]

I have a column of data where each one has a varying number of letters and numbers.

ACB-LCEG253
ACB-4840
ACB-PDG27451
PDG27499
PDG27422-01
PDG27422-02

etc.

I would like to create another column with a formula that shows just the numeric portion of each cell.

Any suggestions would be appreciated.

 

[MWilliams]

Or, is there a way to sort this original data by the numbers in each string, ignoring the letters in the same string?

 

[Domski]

This UDF will do that for you if it's any help:

Function ExtractDigits(s As String) As String
 
    With CreateObject("vbscript.regexp")
        .Pattern = "\D"
        .Global = True
        ExtractDigits = .Replace(s, "")
    End With

End Function

Dom

 

[davesweep]

Hi,

This array might work for you:

=NPV(-0.9,,IF(ISERR(MID(A1,256-COLUMN(A:IV),1)%),"",MID(A1,256-COLUMN(A:IV),1)%))

 

[MWilliams]

Thanks for the replies. I was hoping to do this with a formula, but will use a macro if necessary.

Hi,

This array might work for you:

=NPV(-0.9,,IF(ISERR(MID(A1,256-COLUMN(A:IV),1)%),"",MID(A1,256-COLUMN(A:IV),1)%))

I'm not having any luck with this yet. If my data is in A2:A30, what changes are needed to the formula?

Thanks again!

 

[Domski]

Works for me. Did you confirm it with Ctrl+Shift+Enter?

Dom

 

[Domski]

@Sweep - I've not seen NPV used like that before. How does it work?

Dom

 

[MWilliams]

I've never known about Ctrl+Shift+Enter!

That's what fixed it! Thanks you both!

 

[davesweep]

Dom,

I'm ashamed to say that I pinched it from somewhere and stored it for later use during some googling.

 

[Joe4]

@Sweep - I've not seen NPV used like that before. How does it work?

I'd love to see an explanation of how/why that works too!