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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

I
  • Question
Copy worksheet but the new worksheet to omit some items
2
Replies
11
Views
469
ipbr21054
I
J
  • Solved
Extract multiple numbers from an horizontal string to vertical cells
Replies
9
Views
361
Fluff
Fluff
Q
  • Question
Text Conversion to Numbers - Advanced
Replies
3
Views
464
Sufiyan97
Sufiyan97
F
  • Question
Formula Help: Summing numbers from text cells, that occur before/after specific characters
Replies
3
Views
576
ExceLoki
ExceLoki
T
  • Solved
extracting 6 digit number from text field
Replies
3
Views
207
Fluff
Fluff

Forum statistics

Threads
1,215,463
Messages
6,124,963
Members
449,200
Latest member
indiansth

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