CODEWITHSUNDEEP
excelexcel-formula
Lzypenguin
Lzypenguin

Reputation: 955

Excel formula to check if the character before a specific character is a number or not

I am trying to pull a number within ()'s in the text of a cell. I am using this formula.

=MID(LEFT(A3,FIND(")",A3)-1),FIND("(",A3)+1,LEN(A3))

And it works perfectly except on a few cells. Some of the cells might look like this.

random text (09-12)
random Text Here (comment) more text (2018)
random text (2008)
random Text Here (comment) more text (04-08) more text here

I need to specifically only pull the number from within the parenthesis IF what is in the parenthesis is a number. Any help is appreciated.

To be clear, in the cell there will be text, followed by () with a date range in it. Sometimes it will just be (2009) i need to pull. Sometimes it will be (08-12). But in some of the columns, there is text followed by a parenthesis with a comment, and then more text, and then the parenthesis with the year information. I need to skip the first parenthesis since there is not comment, and only extract the year information from the () with numbers in it.

Upvotes: 1

Views: 839

Answers (2)

MZG
MZG

Reputation: 51

you can use macro function for this. This macro returns the number inside the paranthesis and it assumes that if there is only one "-" character between numbers, it is a number. To remove this assumption change the If (IsNumeric(Replace(result, "-", "", 1, 1, vbTextCompare))) Then to If (IsNumeric(result)) Then.

assume that your excel file name is Book1 and your random Text Here (comment) more text (9999) is located at cell A1

  1. press ALT+F11. This will open the code editor
  2. Right click on the VBAProject(Book1) and select Insert->Module
  3. Copy and paste this macro inside the blank Module1
Function NumberInside(rng As Range, Optional startDelimiter As String = "(", Optional endDelimiter As String = ")")

    Dim txt As String, result As String, finalresult As String

    Dim startIndex As Integer, endIndex As Integer
    startIndex = 1
    txt = rng.Value
    result = ""
    finalresult = result
    Do Until startIndex = 0
        startIndex = InStr(startIndex, txt, startDelimiter, vbTextCompare)
        If (startIndex > 0) Then endIndex = InStr(startIndex + 1, txt, endDelimiter, vbTextCompare) Else Exit Do

        result = Mid(txt, startIndex + 1, endIndex - startIndex - 1)

        If (IsNumeric(Replace(result, "-", "", 1, 1, vbTextCompare))) Then
            finalresult = result
            Exit Do
        End If

        startIndex = startIndex + 1
    Loop

    NumberInside = finalresult

End Function

4.Now you can use the macro in your excel cell. Write NumberInside(A1) into cell B1

5.[optional] if you need to use angle parenthesis instead, you can write NumberInside(A1;"<";">")

Sample data results

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96763

We need to find a special (. That is ( followed by a numeral. We can use:

=MIN(FIND({"(1","(2","(3","(4","(5","(6","(7","(8","(9","(0"},A1 & "(1(2(3(4(5(6(7(8(9(0"))

enter image description here

So our extraction formula is:

=MID(A1,B1+1,FIND(")",A1,B1)-B1-1)

enter image description here

(you can avoid the "helper" cell by replacing each of the three B1s with the first formula.)

=MID(A1,MIN(FIND({"(1","(2","(3","(4","(5","(6","(7","(8","(9","(0"},A1 & "(1(2(3(4(5(6(7(8(9(0"))+1,FIND(")",A1,MIN(FIND({"(1","(2","(3","(4","(5","(6","(7","(8","(9","(0"},A1 & "(1(2(3(4(5(6(7(8(9(0")))-MIN(FIND({"(1","(2","(3","(4","(5","(6","(7","(8","(9","(0"},A1 & "(1(2(3(4(5(6(7(8(9(0"))-1)

But this is just plain ugly.

Upvotes: 1

Related Questions