Extract numbers from a cell

[Martynrbell]

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:

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 quota

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

<tbody>
</tbody>

 

[FormR]

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
	A	B
1	Quota Alert: User vend-scader-casawdco-467191056786938 disabled due to exceeded quota	467191056786938
2	Quota Alert: User vend-baawd-amaadwn-til-cashult-467190456378662 disabled due to exceeded quota	467190456378662
3	Quota Alert: User vend-fsefs-pico-467190003457055 disabled due to exceeded quota	467190003457055
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)),255))," ",REPT(" ",255)),255))

 

[Joe4]

Here is an alternative VBA solution for you, if you are interested. I created my own function to do it.

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!

 

[Martynrbell]

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 "-"

Quota Alert: User mean-sefsef-cassefsefhlesspies-4671910363466938 disabled due to exceeded quota

Quota Alert: User mean-basefesfxter-asefsemazon-til-cashult-46719001346362 disabled due to exceeded quota

Quota Alert: User mean-desefesfmb-pies-4671900081123555 disabled due to exceeded quota

Quota Alert: User mean-vmsefesfan-bulkmean-cashult-466456019377932 disabled due to exceeded quota

Quota Alert: User mean-vsefesfman-bulkvesefsefnd-cashult-46745770019377904 disabled due to exceeded quota

Quota Alert: User mean-vmsefsan-vsol-cashult-467199745680 disabled due to exceeded quota

Quota Alert: User mean-demsefsefb-pies-46719234088547 disabled due to exceeded quota

Quota Alert: User mean-vmsefsefan-bs-amaz-awdfav-cashult-467190019752352271 disabled due to exceeded quota

Quota Alert: User mean-desfsefmb-pies-46719023520 disabled due to exceeded quota

Quota Alert: User mean-schaesefsefrer-fesefscpies-467191023423438 disabled due to exceeded quota

Quota Alert: User mean-demsefsefb-cashlesefsefsspies-4671900156856846 disabled due to exceeded quota

Quota Alert: User mean-schaseferer-cashlfsefesspies-46719100556856804 disabled due to exceeded quota

Quota Alert: User mean-meanmsefan-ssefsefse-cashult-46719008654511 disabled due to exceeded quota

Quota Alert: User mean-desfsefb-pies-467190009752728 disabled due to exceeded quota

Quota Alert: User mean-schasefeserer-fesefscpies-46719100333333 disabled due to exceeded quota

Quota Alert: User mean-baxsefster-sfe-til-cashult-46719003333440 disabled due to exceeded quota

Quota Alert: User mean-vmsefan-vsol-cassefhult-4671900553433 disabled due to exceeded quota

Quota Alert: User mean-schaersefsefer-fesefscpies-46719100654647 disabled due to exceeded quota

Quota Alert: User mean-demsefsefb-pies-4671900093456456 disabled due to exceeded quota

Quota Alert: User mean-seffes-psefico-46719003453410 disabled due to exceeded quota

Quota Alert: User mean-vman-bs-rbsefses-rocket-cashult-467190034534661 disabled due to exceeded quota

Quota Alert: User mean-meanmdrgdranupefetons-fesefscultra-423423463088 disabled due to exceeded quota

Quota Alert: User mean-venawdgfsdrgdman-fesefscultra-412312349237297 disabled due to exceeded quota

Quota Alert: User mean-vmaawdeawdn-vsol-cashult-467134534369284 disabled due to exceeded quota

Quota Alert: User mean-defesfmb-cashpies-4671242349745208 disabled due to exceeded quota

Quota Alert: User mean-schaawdawderer-fesefscpies-46435345701923 disabled due to exceeded quota

Quota Alert: User mean-demsefsefb-fesefscpies-462345437225 disabled due to exceeded quota

Quota Alert: User mean-vmasefsen-bs-rbs-rocket-cashult-234019362572 disabled due to exceeded quota

Quota Alert: User mean-awdewad-fesefscpies-2345435535702204 disabled due to exceeded quota

Quota Alert: User mean-afdawdawd-pies-34534521342728 disabled due to exceeded quota

<tbody>
</tbody>

THanks
again

 

[Martynrbell]

This works perfect also, i will use this on my personal sheets thank you

 

[Martynrbell]

Above was for this

Here is an alternative VBA solution for you, if you are interested. I created my own function to do it.

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!

 

[Martynrbell]

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

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

	A	B
1	Quota Alert: User vend-scader-casawdco-467191056786938 disabled due to exceeded quota	467191056786938
2	Quota Alert: User vend-baawd-amaadwn-til-cashult-467190456378662 disabled due to exceeded quota	467190456378662
3	Quota Alert: User vend-fsefs-pico-467190003457055 disabled due to exceeded quota	467190003457055

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)),255))," ",REPT(" ",255)),255)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[AlKey]

Or this
Enter in B1 and copy down

[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
	A	B
1	Quota Alert: User mean-sefsef-cassefsefhlesspies-4671910363466938 disabled due to exceeded quota	4671910363466938
2	Quota Alert: User mean-basefesfxter-asefsemazon-til-cashult-46719001346362 disabled due to exceeded quota	46719001346362
3	Quota Alert: User mean-desefesfmb-pies-4671900081123555 disabled due to exceeded quota	4671900081123555
4	Quota Alert: User mean-vmsefesfan-bulkmean-cashult-466456019377932 disabled due to exceeded quota	466456019377932
5	Quota Alert: User mean-vsefesfman-bulkvesefsefnd-cashult-46745770019377904 disabled due to exceeded quota	46745770019377904
Sheet1