1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract numbers after character

Discussion in 'Ask an Excel Question' started by IZ2018, Jan 25, 2018.

  1. IZ2018

    IZ2018 Member

    Messages:
    36
    I have this string :
    A29 B500 A41 A56 D24 A98 K67 A14 A151 (A20 dia, go SETTING 1)
    or
    J21 A45 (go Setting 2)
    or
    G56 A98 A55 M50 A25

    I would like to extract numbers after character A so from first string the the result should be : 20,41,56,98,14,151
    Maximum repetition of A is 6.
    Could you help with a formula on this ?
    If possible ONLY, I do not want to extract anything between ().

    Thanks all
    Last edited: Jan 25, 2018
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,260
    IZ2018

    Firstly, Welcome to the Chandoo.org Forums

    It should be:
    I would like to extract numbers after character A so from first string the the result should be : 29,41,56,98,14,151
  3. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,594
    upload_2018-1-25_12-44-31.png

    If you have Excel 2013 or above, you can use FILTERXML() to split the specified data.

    1] In B2, formula copied across and down :

    =IFERROR(MID(FILTERXML("<x><a>"&SUBSTITUTE(LEFT($A2,FIND("(",$A2&"(")-1)," ","</a><a>")&"</a></x>","//*[starts-with(text(),'A')]["&COLUMNS($A:A)&"]"),2,16),"")

    2] See attached file.

    Regards
    Bosco

    Attached Files:

    IZ2018, GraH - Guido, Hui and 2 others like this.
  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,594
    Or.... another option,

    In B2, copied across and down :

    =IFERROR(LOOKUP(9^9,0+MID($A2,FIND("@",SUBSTITUTE(LEFT($A2,FIND("(",$A2&"(")-1),"A","@",COLUMNS($A:A)))+1,ROW($1:$16))),"")

    Regards
    Bosco

    Attached Files:

  5. Nebu

    Nebu Excel Ninja

    Messages:
    2,064
    Hi:

    May be this VBA
    Code (vb):
    Sub test()
    Application.ScreenUpdating = False
    Dim j&, k&

    For l& = 2 To 4
        m& = Len(Range("A" & l))
        m = m - Len(WorksheetFunction.Substitute(Range("A" & l), "A", ""))
            For i& = 1 To m
                If InStr(j + 1, Range("A" & l), "A") > 0 Then
                    j = InStr(j + 1, Range("A" & l), "A") + 1
                    k = InStr(k + 1, Range("A" & l), " ") - 1
                    Range("B" & l) = Trim(Range("B" & l) & "," & Mid(Range("A" & l), j, k))
                End If
            Next
        s$ = Mid(Range("B" & l), 2, Len(Range("B" & l)))
        Range("B" & l) = s
        j = 0
        k = 0
    Next

    Application.ScreenUpdating = True
    End Sub
     
    Thanks

    Attached Files:

  6. IZ2018

    IZ2018 Member

    Messages:
    36
    Sorry for my very bad explanation on first post. The numbers extracted after Character A should go in ONE column, named A. Please see attached file. If A is unique in string, like A20 G12 N22, put in Column named A the number 20. If A has multiple repetitions, like A20 G3 A33 A98 K22 A126 then put in Column named A the numbers 20,33,98,126. And if in string is text in parenthesis like A25 C45 A33 A97 (A66 diameter, go to Setting 1) I do not want to "look/extract" between parenthesis so 66 is not extracted. I hope attached file make more sense. Sorry all and thanks again

    Attached Files:

  7. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,855
    Does your real data have multiple pairs of brackets like:
    A33 (A44) (A55)
    or is it just one pair always?
  8. IZ2018

    IZ2018 Member

    Messages:
    36
    No, the text has one single bracket per string and is at the end of string.
    A20 G19 A33 A44 N23 A55 (if A45 mm dia go to setting15)
    The reason I do not want to look between brackets is that it can extract unwanted numbers like 45.
  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,457
    Hi ,

    Doesn't Bosco's answer give you what you want ? All you have to do is concatenate the results from 6 columns.

    See the attached file.

    Narayan

    Attached Files:

    Thomas Kuriakose and bosco_yip like this.
  10. IZ2018

    IZ2018 Member

    Messages:
    36
    I could but then, starting from one column (String Column) I need a lot of Helper Columns to get the result back in one column. I am happy with all the ideas I got so far. The formula should be like : see where in String is the character "(" and do not look in right side of it, and then find first A, extract the numbers to first space, then find the next A and extract the numbers to next space, and so on until "("
    I found how to extract numbers after A and next space, if A is unique in String.
    I also, to Remove condition of "(" , I can SPLIT String in 2, move text in () to another column.
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,457
    Hi ,

    You would need exactly 6 columns.

    Excel has 16384.

    Narayan
  12. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,855
    You can use below UDF.

    In VBE add a general module and paste below code:
    Code (vb):
    Public Function ExtractNumbers(rngInput As Range, Optional varDelim, Optional varChr) As String
    Dim varOut
    Dim i As Long

    If IsMissing(varDelim) Then varDelim = ","
    If IsMissing(varChr) Then varChr = "A"

    If InStr(1, rngInput.Value, "(", vbTextCompare) = 0 Then
        varOut = Split(Trim(rngInput.Value), " ")
    Else
        varOut = Split(Trim(Left(rngInput.Value, InStr(1, rngInput.Value, "(", vbTextCompare) - 1)), " ")
    End If

    For i = LBound(varOut) To UBound(varOut)
        If Left(varOut(i), 1) = CStr(varChr) Then
            ExtractNumbers = ExtractNumbers & " " & Replace(varOut(i), CStr(varChr), "")
        End If
    Next i
    ExtractNumbers = Replace(Trim(ExtractNumbers), " ", varDelim)

    End Function
    The use it like : =ExtractNumbers(A3)

    I have kept optional arguments to change letters and delimiters.

    e.g. =ExtractNumbers(A3,";","B")
  13. IZ2018

    IZ2018 Member

    Messages:
    36
    Thank you all. The code work well but since my VBA understanding is close to zero, I have to use the above formulas. I also lost Undo/Redo with VBA so I do not know how to debug. I have learned a lot from you. My challenge now is to try nest somehow the formula above from Bosco_yip so I can get the numbers in one column. I found the numbers between first A and space after. How can I say "NEXT" I mean, do the same for next A and next space until characte "("
  14. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,428
    Don't be afraid to use helper columns.

    Often, doing calculation steps in helper column(s) is far more efficient than doing it in single column/cell.

    As well, it's easier for your successor to understand the spreadsheet, if and when you move on to something else. Or even for yourself, when you need to change something in the set up down the line.
    GraH - Guido likes this.
  15. GraH - Guido

    GraH - Guido Active Member

    Messages:
    521
    Dear bosco_yip, just to say, the way you built formulas is so inspiring, it actually hurts.
    Khalid NGO and shrivallabha like this.
  16. IZ2018

    IZ2018 Member

    Messages:
    36
    Thank you all, both formulas from above and VBA work great.
  17. IZ2018

    IZ2018 Member

    Messages:
    36
    I have a new question related to this post.
    This time, the characters in String are single/unique but not in alphabetical order.
    So String looks like : A100 F25 K29 C531 X44 (This K22 is a Text)
    The same, extract until Character "(" if any in the string
    I would like if possible to EXTRACT the numbers to CORRESPONDED Column !
    So in my Table, 100 go to Column named A, 25 go to Column named F...
    All columns are created, because I know all characters possible in string
  18. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,594
    Perhaps,

    upload_2018-1-29_8-43-17.png

    In B2, copied across and down :

    =IFERROR(LOOKUP(9^9,0+MID($A2,FIND(B$1,$A2)+LEN(B$1),ROW($1:$15))),"")

    Regards
    Bosco
  19. IZ2018

    IZ2018 Member

    Messages:
    36
    The Formula is looking between () and like I said above I only want to look UNTIL the Character "(" so in the LEFT side of this character
  20. IZ2018

    IZ2018 Member

    Messages:
    36
    My ultimate goal is explained in attached file.

    Attached Files:

  21. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,855
    I was kind of expecting this and UDF was adjusted for it (and usage explained in Post #12).

    See attached demo.

    Attached Files:

  22. IZ2018

    IZ2018 Member

    Messages:
    36
    01. Could you PLEASE explain, in formula bellow, the meaning of : 9^9 and $1:$16 ?


  23. Khalid NGO

    Khalid NGO Excel Ninja

    Messages:
    1,916
    Hi,

    9^9 is generally referenced to return a big number in excel, often used to find the last value in a given range.

    ROW($1:$16) is used in Bosco's formula to return the array of numbers from 1 to 16 i.e. {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}

    HTH,
  24. IZ2018

    IZ2018 Member

    Messages:
    36
    Could you help on this STRINGS SPLIT ?
    A10 B20 C30 (TEXT) K40 S500
    A300 G40

    a) If () exist in string then : extract outside () like A10 B20 C30 K40 S500
    b) And If () do not exist in string then keep the string as is like A300 G40

Share This Page