vba to extract numbers

[Bartexcel]

I need VBA to extract the numbers from a cell where specific alphas precede. example - big 1234 = 1234 or Big 1234 = 1234 or large 1234 = 1234 or grand 4321-01 = 4321-01 or Lg. 5678 = 5678.

 

[Sufiyan97]

If you are happy with formula

Book2
	A	B	C
1	big 1234	1234
2	Big 1234	1234
3	large 1234	1234
4	grand 4321-01	4321-01
5	Lg. 5678	5678
6
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=MID(A1,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0,"-"},A1),1),99)

 

[JEC]

Is it always like: text - space - numbers?

Sub jec()
 For Each it In Range("A1:A5")
    it.Offset(, 1).Value = Split(it)(1)
 Next
End Sub

 

[Joe4]

Welcome to the Board!

If you always have the number after the last space in your text, this would also work:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

 

[Bartexcel]

If you are happy with formula

Book2
	A	B	C
1	big 1234	1234
2	Big 1234	1234
3	large 1234	1234
4	grand 4321-01	4321-01
5	Lg. 5678	5678
6
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=MID(A1,AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0,"-"},A1),1),99)

I'm smiling - only that - Big, big, large, lg. and/or grand are specific text that i look for because users will enter data according to their individualized word preferences in the description, and the descriptions can get very lengthy. #tiredeyes Also it would help to calculate the count of numbers rather than ,99) Thank you.

 

[Sufiyan97]

I'm smiling - only that - Big, big, large, lg. and/or grand are specific text that i look for because users will enter data according to their individualized word preferences in the description, and the descriptions can get very lengthy. #tiredeyes Also it would help to calculate the count of numbers rather than ,99) Thank you.

I don't understand what you are trying to say
Will the formula help or not?

 

[Bartexcel]

Is it always like: text - space - numbers?

Sub jec()
 For Each it In Range("A1:A5")
    it.Offset(, 1).Value = Split(it)(1)
 Next
End Sub

Could be. It's more like - Big, big, large, lg. and/or grand are specific text that i look for because users will enter data according to their individualized word preferences in the description, and the descriptions can get very lengthy. #tiredeyes Also it would help to calculate the count of numbers so i get just the number. Thank you.

 

[Joe4]

and the descriptions can get very lengthy.

It would be best if you could show us some different examples of what the data may really look like.
It is best not to oversimplify things when posting your questions, or else you may get answers that work for your oversimplified examples, but not for your real data.

 

[JEC]

This will probably do

Function jec(cell As String) As String
 ar = Split(cell)
 For i = 0 To UBound(ar)
   If ar(i) Like "[0-9]*" Then
      jec = ar(i)
      Exit Function
   End If
 Next
End Function

=jec(A1)

 

[Bartexcel]

I don't understand what you are trying to say
Will the formula help or not?

sorry - admin won't allow XL2BB - i need vba or formula to find keywords like "large" then extract the number that follow see image.