Extract only the numbers

G

gpapagre

New Member
Joined
Mar 6, 2009
Messages
18
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Is there any consistant pattern to the way they appear?
In your given example, it looks like it might be the last 4 digits ?

=RIGHT(A1,4)+0
 
Upvote 0
Supposing that data is in cell A1.

This formula will extract the numbers in order.
Code: 
=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
    *ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)
    *10^ROW(INDIRECT("1:"&LEN(A1)))/10)

When this formula is typed into the formula bar, the entry is completed with CTRL+SHIFT+ENTER, not just ENTER. This should put braces { } around the formula.

This is taken from these threads:
http://www.excelforum.com/excel-wor...acting-numbers-from-alphanumeric-strings.html
http://www.mrexcel.com/forum/showthread.php?t=362184
 
Upvote 0
Hi,

You could use this UDF maybe?

Code: 
Function CleanUp(Txt)

For x = 1 To 255
    Select Case x
        Case 45, 47, 65 To 90, 95, 97 To 122
        Txt = WorksheetFunction.Substitute(Txt, Chr(x), "")
    End Select
Next x

CleanUp = Txt

End Function

In Excel, Hit Alt & F11 together, find the workbook your using, Right Click, Insert Module and paste this in. Then in Excel use this formula.

=CLEANUP(A1)

And it should remove all your extra's.
 
Upvote 0
Yea Sir, always are separated from the text using different characters and letters like .../ -

No Sir, there is no consistent pattern. This is due to the fact that different clients use different accounting softwares.

99% of the times are separated from the text using commas, symbols like / _ - etc

thank you
 
Upvote 0
Thank you Mike, Thank you All
I shall try what you have suggested to me the next days, unfortunately I have to shut down for back up.

I thank you all in advance

George
 
Upvote 0
Probably a more complete solution to my previous would be this as it should extract everything that isn't a number;

Code: 
Function CleanUp(Txt)

For x = 1 To 255
    Select Case x
        Case 1 To 47, 58 To 255
        Txt = WorksheetFunction.Substitute(Txt, Chr(x), "")
    End Select
Next x

CleanUp = Txt

End Function
 
Upvote 0
Another option for a UDF:

Code: 
Function OnlyDigits(s As String) As String
 
With CreateObject("vbscript.regexp")
    .Pattern = "\D"
    .Global = True
    OnlyDigits = .Replace(s, "")
End With
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
How autonumber in textbox on userform based on click three buttons
2 3
Replies
20
Views
599
Omar M
O
C
  • Question Question
Need help! extract value in special case, find many method, cannot solve it!
Replies
6
Views
143
CLKinfo
C
T
  • Solved
Extracting and Formatting Numbers from Data Rows
2
Replies
12
Views
339
TheWes
T
C
  • Solved
Extract 8 character number from text string
2
Replies
15
Views
747
Fluff
Fluff
I
  • Question Question
Copy worksheet but the new worksheet to omit some items
2
Replies
11
Views
893
ipbr21054
I

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,646
Latest member
tudou

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top