FIND and SEARCH numbers from String

[unknownymous]

Hello Guys,

I was looking for the codes where I can search for the numbers on the data below so basically it's the [**]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Subject[/TD]
[TD]Status[/TD]
[TD]Note[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Science [1][/TD]
[TD]YES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Math [49][/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Health [9][/TD]
[TD]YES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sports[/TD]
[TD]YES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]History [29][/TD]
[TD]YES[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Once I run the codes, I can have below data (number sorted ascending) on another sheet called "Check"

After:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Subject[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Science[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Health[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]History[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]Math[/TD]
[TD]49[/TD]
[/TR]
</tbody>[/TABLE]


Any help will be much appreciated.

 

[Drrellik]

If you are looking for a formula you can use Find to locate the left [ and the right ] and MID out your numbers like this

=IFERROR(MID(B2,(FIND("[",B2)+1),(FIND("]",B2))-(FIND("[",B2)+1)),"")

I used your table and put the formula in E2 where your data started in A2

HTH ~DR


You can also take a look here and modify this to suit your needs if you like this better.
https://www.ablebits.com/office-addins-blog/2017/11/22/excel-extract-number-from-string/

 

[unknownymous]

Thank you! Can I use this code in case I want to create a macro?

 

[Rick Rothstein]

Thank you! Can I use this code in case I want to create a macro?

Here is a macro solution for you to try...

[table="width: 500"]
[tr]
	[td]Sub SubjectsAndNumbers()
  Dim R As Long, SubjNum As Variant, Result As Variant
  SubjNum = Range("B2", Cells(Rows.Count, "B").End(xlUp))
  ReDim Result(1 To UBound(SubjNum), 1 To 2)
  For R = 1 To UBound(SubjNum)
    If SubjNum(R, 1) Like "*[[]*" Then
      Result(R, 1) = Left(SubjNum(R, 1), InStr(SubjNum(R, 1) & "[", "[") - 1)
      Result(R, 2) = Val(Mid(SubjNum(R, 1), InStr(SubjNum(R, 1) & "[", "[") + 1))
    End If
  Next
  With Sheets("Check")
    .Range("A1:B1") = Array("Subject", "Number")
    .Range("A2").Resize(UBound(Result), 2) = Result
    .Range("A2:B" & UBound(Result) + 1).Sort Key1:=.Range("B2")
  End With
End Sub[/td]
[/tr]
[/table]

 

[Peter_SSs]

Assuming that ..
- the original data is in the active sheet when the code is run and that the data is in columns A:D, and
- sheet Check exists with no current data in columns A:B
.. you could try this non-looping method (in a copy of your workbook)

Sub SubjectNum()
  With Sheets("Check").Range("A1").Resize(Range("B" & Rows.Count).End(xlUp).Row)
    .Value = Range("B1", Range("B" & Rows.Count).End(xlUp)).Value
    .Replace What:="]", Replacement:="", LookAt:=xlPart
    .Replace What:=" [", Replacement:="[", LookAt:=xlPart
    .TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="["
    .Resize(, 2).Sort Key1:=.Columns(2), Order1:=xlAscending, Header:=xlYes
    .Offset(1, 1).SpecialCells(xlBlanks).EntireRow.Delete
    .Cells(1, 2).Value = "Number"
  End With
End Sub

 

[unknownymous]

This is great! Thanks Rick & Peter

 

[Peter_SSs]

This is great! Thanks Rick & Peter

Cheers. Glad you have a successful outcome.