Extract only the numbers

[gpapagre]

Dear all,

thanks for your hospitality. this is the second time i post a question to this board, hopefully i will get the same help as in the first time when NMeeker solved my problem with missing invoices. i thank him very much.

my question is simple.

In column A I have a list of numbers which unfortunalely sometimes when I extract them from accounting softwares are in the form of letters, commas etc plus the numbers. examples: INVOICE_5526, INV-2343, INV/3369 and in other formats. What I want to have in column B is just the numbers.

Is there a formula I can apply in any case to isolate only the numbers?

Thank you all
George

 

[pgc01]

In your examples the numbers are always the last part of the string.

If that's always the case, for numbers with less that 10 digits:

=LOOKUP(10^10,--RIGHT(A7,{1,2,3,4,5,6,7,8,9}))

 

[Aladin Akyurek]

Also:

=REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,"")+0

if only the last part is numeric as in INVOICE_5526, INV-2343, and INV/3369.

 

[Colin Legg]

Ah ok... I thought that example was all 1 string in one cell!

 

[gpapagre]

Hello, thanks for your macro. have tried it but doesnt work. can you help?

Another option for a UDF:

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

 

[gpapagre]

In your examples the numbers are always the last part of the string.

If that's always the case, for numbers with less that 10 digits:

=LOOKUP(10^10,--RIGHT(A7,{1,2,3,4,5,6,7,8,9}))

thanks,
can you tell me please...when i copy / paste this formula to my excel 2003 doesnt work. do i do anything wrong?

 

[pgc01]

I just tried it in xl2000 with no problem. Did you replace A7 with the address of the input cell?

 

[gpapagre]

I just tried it in xl2000 with no problem. Did you replace A7 with the address of the input cell?

hI SIR, the problem is with me,not your formula. have tried again with other formulas and dont function.

i highlight and then copy / paste the formula to my formula bar or in B1. say i have A1:A100 (100 numbers with letters).

 

[Ron Coderre]

With
A1: (an invoice reference)

Try this:

B1: =--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

Examples:
INV-1000......Formula returns: 1000
INV/4321......Formula returns: 4321
INV.98764.....Formula returns: 98764

Does that help?

 

[gpapagre]

With
A1: (an invoice reference)

Try this:

B1: =--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

Examples:
INV-1000......Formula returns: 1000
INV/4321......Formula returns: 4321
INV.98764.....Formula returns: 98764

Does that help?

hi, thank you. when i copy/paste the above formula in B1 doesnt do anything. Wondering why is that happening not only with this formula but also with almost all others. can you advice?

 

[Aladin Akyurek]

hi, thank you. when i copy/paste the above formula in B1 doesnt do anything. Wondering why is that happening not only with this formula but also with almost all others. can you advice?

There must be happening something. Are you seeing the formula instead of a result?