Extracting Multiple Numbers from String

[bolillo1234]

I have thousands of fields that contain addresses and I need to extract only the numbers. The problem I am facing is that there are two numbers within the string and they begin at different times.

Examples of the data
1409 N 250 W
259 West 158 Johnson Blvd
109829 South 344 East

What Im trying to get
1409250
259158
109829344

Thanks,
Bolillo

 

[Rick Rothstein]

I have one question. I have to extract particular number.

Eg:

E2 Abc21mhz

I want to extract just 21.

Can any one please tell me how can I in excel?

Is E2 part of the text in the cell or is it there to tell us your text is in cell E2?

 

[fayraree]

Hi all! First post here! I've been lurking and reading up on functions on this forum for years, but decided to finally register and ask a question! Thank you all for all your efforts and sharing of your awesome skills...much respect!

I think this thread is the best place to ask because in essence, I'm also trying to extract numbers for a cell, but my case is a bit different.

The string in my cell is a group of numbers which are a series of add/subtract functions, for example:

"=9555-29-1000-1000-1000-29-4770"

Some context: I'm trying to build a report which would automatically import numerical data from another excel file. The source has many cells where data has been entered manually as shown above. The target report would take the data from the source, and put the first digit (positive number) in a particular cell, and ADD the subsequent negative numbers and place the absolute value in a second cell. Basically, net numbers have been entered in the source file and I would like to break out the positives and negatives in the target report.

The quantity of +/- numbers in each cell varies; the first number is always a positive, but the quantity of negative numbers (6 in the above example) may vary. There may also be 2 positive numbers in the beginning, followed by a string of negatives.

Does anyone have an easy way to take care of this issue? Thanks in advance!

 

[István Hirsch]

Sum of the positive numbers (1 or 2 numbers):

=LEFT(A1,MIN(FIND({"-","+"},A1&"+-"))-1)+IF(COUNTIF(A1,"*+*")=1,TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",50)),FIND("+",A1),50)),0)

Sum of the negative numbers (set to max. 12 numbers):

=SUM(1*TRIM(MID(SUBSTITUTE(A1&REPT("-0",12),"-",REPT(" ",100)),{1,2,3,4,5,6,7,8,9,10,11,12}*100,100)))

Excel Workbook
	A	B	C
1	9555-29-1000-1000-1000-29-4770	9555	7828
2	9555+45-29-1000-1000-1000-29-4770	9600	7828
3	1+1-45-23	2	68
4	43-1-2-3-4-5-6-789-1-1-1-1	43	814
5	5000+111-45	5111	45
6	5000-111	5000	111
7	100001+220002-56-5678932	320003	5678988
8
Sheet

 

[smgwr]

Welcome to the Board!

Since all the numbers are not together I believe this can only be done with a user-defined function and not with excel's native functions. You can use this in a module:

'--------------------------------------------------------------------------------------------------------
'***NEW FUNCTION***
'Description: Separates Alphabetic and Numeric Data.  (i.e. Alpha 123, Alpha123)
'             True or 1 will return alphabet, False or 0 will return numeric
'Example:   A1 value is Rob12Schiele34
'           =Sep(A1,1)      Returns:    RobSchiele
'           =Sep(A1,0)      Returns:    1234
Function Sep(txt As String, flg As Boolean) As String
With CreateObject("VBScript.RegExp")
    .Pattern = IIf(flg = True, "\d+", "\D+")
    .Global = True
    Sep = .Replace(txt, "")
End With
End Function

Hope that helps.

Is there anyway to get this module to recognise decimal points with the numbers?

For instance, when asking it to get the numbers from "0.5 GHFWWF", it will return "05". Is there anything that can be tweaked to this that would get it to return "0.5"?

 

[Rick Rothstein]

Is there anyway to get this module to recognise decimal points with the numbers?

For instance, when asking it to get the numbers from "0.5 GHFWWF", it will return "05". Is there anything that can be tweaked to this that would get it to return "0.5"?

Here is a different macro that you can use (it returns the first number in the text; this differs from the function you posted which returns all the digits in the text even if those digits are not contiguous)...

[table="width: 500"]
[tr]
[td]

Function GetNum(ByVal S As String, Optional AllowNegatives As Boolean) As Double
  Dim X As Long, Value As Variant
  If Not AllowNegatives Then S = Replace(S, "-", "z")
  S = Replace(Replace(S, "e", "z", , , vbTextCompare), "d", "z", , , vbTextCompare)
  For X = 1 To Len(S)
    If Mid(S, X) Like "[+-.]#*" Or Mid(S, X) Like "[0-9]*" Then
      GetNum = Val(Mid(S, X))
      Exit Function
    End If
  Next
End Function

[/td]
[/tr]
[tr]
[td][/td]
[/tr]
[tr]
[td][/td]
[/tr]
[/table]

Note: This function has an optional Boolean argument controlling whether negative values will be returned or not... True will allow them to be returned, False (the default) will ignore minus signs and simply return the unsigned number.

 

[smgwr]

Here is a different macro that you can use (it returns the first number in the text; this differs from the function you posted which returns all the digits in the text even if those digits are not contiguous)...

[table="width: 500"]
[tr]
[td]

Function GetNum(ByVal S As String, Optional AllowNegatives As Boolean) As Double
  Dim X As Long, Value As Variant
  If Not AllowNegatives Then S = Replace(S, "-", "z")
  S = Replace(Replace(S, "e", "z", , , vbTextCompare), "d", "z", , , vbTextCompare)
  For X = 1 To Len(S)
    If Mid(S, X) Like "[+-.]#*" Or Mid(S, X) Like "[0-9]*" Then
      GetNum = Val(Mid(S, X))
      Exit Function
    End If
  Next
End Function

[/td]
[/tr]
[tr]
[td][/td]
[/tr]
[tr]
[td][/td]
[/tr]
[/table]

Note: This function has an optional Boolean argument controlling whether negative values will be returned or not... True will allow them to be returned, False (the default) will ignore minus signs and simply return the unsigned number.

Fantastic! Works a charm!

Thanks a lot, Rick!

 

[Peter_SSs]

smgwr

Just a few comments ..

1. One example ("0.5 GHFWWF") isn't much to go on but could your problem be as simple as extracting what is to the the left of the first space?

2. Assuming the answer to 1. is "No", then Rick's function may want a tweak (another Replace?) unless you want examples like "Shop 1598 West Street" and "Shop 15 98th Street" to both return 1598.

3. Is it okay that the function 'extracts' the digit 0 from a string that contains no digits at all?

 

[esuerez]

Enjoy It

Function GoNumeric1(target As Range) As Double
    
    Set Rng = target
    
    For I = 1 To Len(Rng)
      letter = Mid(Rng, I, 1) 'each character
      If Asc(letter) > 47 And Asc(letter) < 58 Or Asc(letter) = 46 Then
        finishedString = finishedString + letter
      End If
    Next I
    
    If finishedString <> "" Then GoNumeric1 = finishedString


End Function

 

[manav1103]

Hi,

I want your help.

I want to extract only numbers from right out of a string

Examples of My string are :

1. Asia Pacific Billing ATT-14-015(eForm # 2028264)

2. Asia Pacific Billing ATT-14-018v2(eForm # 2029389)

3. Asia Pacific Billing -eForm # 2024585

Kindly help.

I just want the number shown in from of eform #

 

[István Hirsch]

Give this a try:

Excel Workbook
	A	B
1	1. Asia Pacific Billing ATT-14-015(eForm # 2028264)	2028264
2	2. Asia Pacific Billing ATT-14-018v2(eForm # 2029389)	2029389
3	3. Asia Pacific Billing -eForm # 2024585	2024585
Sheet