Extract numbers from a cell

M

Martynrbell

New Member
Joined
Apr 11, 2016
Messages
38
Office Version
  1. 365
Platform
  1. Windows
Hi all, sorry if this has been done over and over again, i have searched but cant find anything which applies to my query.

i have many cells which contain information like this:

[TABLE="width: 743"]
<tbody>[TR]
[TD]Quota Alert: User vend-scader-casawdco-467191056786938 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User vend-baawd-amaadwn-til-cashult-467190456378662 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User vend-fsefs-pico-467190003457055 disabled due to exceeded quota

i need to extract the numbers only.
i have tried using the left and right formulas however the text either side is not always the same or the number itself is not always the same size.

any ideas on how this can be done im using 2013 edition if this helps

thanks in advance

Martyn[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Hi, is the number always immediately after the last "-" symbol? If not can you post some more representative sample data or define some rules that can be used to find the position of the number?


Excel 2013/2016
AB
1Quota Alert: User vend-scader-casawdco-467191056786938 disabled due to exceeded quota467191056786938
2Quota Alert: User vend-baawd-amaadwn-til-cashult-467190456378662 disabled due to exceeded quota467190456378662
3Quota Alert: User vend-fsefs-pico-467190003457055 disabled due to exceeded quota467190003457055
Sheet1
Cell Formulas
RangeFormula
B1=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)),255))," ",REPT(" ",255)),255))
 
Upvote 0
Here is an alternative VBA solution for you, if you are interested. I created my own function to do it.
Code: 
Function ExtractNum(myEntry As String) As String

    Dim ln As Long
    Dim c As Long
    Dim myString As String
    
    ln = Len(myEntry)
    If ln > 0 Then
        For c = 1 To ln
            If IsNumeric(Mid(myEntry, c, 1)) Then
                myString = myString & Mid(myEntry, c, 1)
            End If
        Next c
    End If
    
    ExtractNum = myString
    
End Function
So, you would use it like any other Excel function i.e.
=ExtractNum(A1)


Edit: I wouldn't have posted this if I had already seen FormR's solution. But I figured since I took the time to write it, I might as well post it!
 
Last edited:
Upvote 0
Hi FormR,
THanks for replying.

Ill post a sample from what i am working with today, the number i want to extract does always start with a "-"


[TABLE="width: 633"]
<tbody>[TR]
[TD]Quota Alert: User mean-sefsef-cassefsefhlesspies-4671910363466938 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-basefesfxter-asefsemazon-til-cashult-46719001346362 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-desefesfmb-pies-4671900081123555 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-vmsefesfan-bulkmean-cashult-466456019377932 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-vsefesfman-bulkvesefsefnd-cashult-46745770019377904 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-vmsefsan-vsol-cashult-467199745680 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-demsefsefb-pies-46719234088547 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-vmsefsefan-bs-amaz-awdfav-cashult-467190019752352271 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-desfsefmb-pies-46719023520 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-schaesefsefrer-fesefscpies-467191023423438 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-demsefsefb-cashlesefsefsspies-4671900156856846 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-schaseferer-cashlfsefesspies-46719100556856804 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-meanmsefan-ssefsefse-cashult-46719008654511 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-desfsefb-pies-467190009752728 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-schasefeserer-fesefscpies-46719100333333 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-baxsefster-sfe-til-cashult-46719003333440 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-vmsefan-vsol-cassefhult-4671900553433 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-schaersefsefer-fesefscpies-46719100654647 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-demsefsefb-pies-4671900093456456 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-seffes-psefico-46719003453410 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-vman-bs-rbsefses-rocket-cashult-467190034534661 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-meanmdrgdranupefetons-fesefscultra-423423463088 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-venawdgfsdrgdman-fesefscultra-412312349237297 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-vmaawdeawdn-vsol-cashult-467134534369284 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-defesfmb-cashpies-4671242349745208 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-schaawdawderer-fesefscpies-46435345701923 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-demsefsefb-fesefscpies-462345437225 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-vmasefsen-bs-rbs-rocket-cashult-234019362572 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-awdewad-fesefscpies-2345435535702204 disabled due to exceeded quota[/TD]
[/TR]
[TR]
[TD]Quota Alert: User mean-afdawdawd-pies-34534521342728 disabled due to exceeded quota[/TD]
[/TR]
</tbody>[/TABLE]


THanks
again
 
Upvote 0
Above was for this

Joe4 said:
Here is an alternative VBA solution for you, if you are interested. I created my own function to do it.
Code: 
Function ExtractNum(myEntry As String) As String

    Dim ln As Long
    Dim c As Long
    Dim myString As String
    
    ln = Len(myEntry)
    If ln > 0 Then
        For c = 1 To ln
            If IsNumeric(Mid(myEntry, c, 1)) Then
                myString = myString & Mid(myEntry, c, 1)
            End If
        Next c
    End If
    
    ExtractNum = myString
    
End Function
So, you would use it like any other Excel function i.e.
=ExtractNum(A1)


Edit: I wouldn't have posted this if I had already seen FormR's solution. But I figured since I took the time to write it, I might as well post it!
Click to expand...
 
Upvote 0
This works perfectly.
ill be able to use this in my shared book which isnt aloud to use scripts.

thanksyou so much for your help

FormR said:
Hi, is the number always immediately after the last "-" symbol? If not can you post some more representative sample data or define some rules that can be used to find the position of the number?

Excel 2013/2016
AB
Quota Alert: User vend-scader-casawdco-467191056786938 disabled due to exceeded quota
Quota Alert: User vend-baawd-amaadwn-til-cashult-467190456378662 disabled due to exceeded quota467190456378662
Quota Alert: User vend-fsefs-pico-467190003457055 disabled due to exceeded quota467190003457055

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FFFF00"]467191056786938[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)),255))," ",REPT(" ",255)),255))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Or this
Enter in B1 and copy down
Code: 
[B]=TRIM(LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),100)," ",REPT(" ",50)),50))[/B]

Book1
AB
1Quota Alert: User mean-sefsef-cassefsefhlesspies-4671910363466938 disabled due to exceeded quota4671910363466938
2Quota Alert: User mean-basefesfxter-asefsemazon-til-cashult-46719001346362 disabled due to exceeded quota46719001346362
3Quota Alert: User mean-desefesfmb-pies-4671900081123555 disabled due to exceeded quota4671900081123555
4Quota Alert: User mean-vmsefesfan-bulkmean-cashult-466456019377932 disabled due to exceeded quota466456019377932
5Quota Alert: User mean-vsefesfman-bulkvesefsefnd-cashult-46745770019377904 disabled due to exceeded quota46745770019377904
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Extracting string of 10 Numbers from cell
2 3
Replies
26
Views
3K
István Hirsch
I
M
  • Question Question
Extracting numbers from cells with text
Replies
5
Views
511
Rick Rothstein
Rick Rothstein
S
  • Question Question
Import Data From Text File - VBA Code Question
Replies
0
Views
8K
ststern45
S

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

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