Extract numbers after character

IZ2018 · Jan 25, 2018

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

Hui · Jan 25, 2018

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

bosco_yip · Jan 25, 2018

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

bosco_yip · Jan 25, 2018

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

Nebu · Jan 25, 2018

Hi:

May be this VBA

Code:

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

IZ2018 · Jan 25, 2018

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

shrivallabha · Jan 25, 2018

Does your real data have multiple pairs of brackets like:
A33 (A44) (A55)
or is it just one pair always?

IZ2018 · Jan 25, 2018

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.

NARAYANK991 · Jan 25, 2018

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

IZ2018 · Jan 25, 2018

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.

NARAYANK991 · Jan 25, 2018

IZ2018 said:
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.

Hi ,

You would need exactly 6 columns.

Excel has 16384.

Narayan

shrivallabha · Jan 25, 2018

You can use below UDF.

In VBE add a general module and paste below code:

Code:

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")

IZ2018 · Jan 25, 2018

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 "("

Chihiro · Jan 25, 2018

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 · Jan 25, 2018

Dear bosco_yip, just to say, the way you built formulas is so inspiring, it actually hurts.

IZ2018 · Jan 25, 2018

Thank you all, both formulas from above and VBA work great.

IZ2018 · Jan 28, 2018

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

bosco_yip · Jan 29, 2018

IZ2018 said:
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

Perhaps,

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

IZ2018 · Jan 29, 2018

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

IZ2018 · Jan 29, 2018

My ultimate goal is explained in attached file.

shrivallabha · Jan 29, 2018

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

See attached demo.

IZ2018 · Feb 3, 2018

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

bosco_yip said:
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

Khalid NGO · Feb 4, 2018

IZ2018 said:
9^9

Hi,

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

IZ2018 said:
$1:$16 ?

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,

IZ2018 · Feb 5, 2018

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

Extract numbers after character

Member

Excel Ninja

Excel Ninja

Attachments

Excel Ninja

Attachments

Excel Ninja

Attachments

Member

Attachments

Excel Ninja

Member

Excel Ninja

Attachments

Member

Excel Ninja

Excel Ninja

Member

Excel Ninja

Well-Known Member

Member

Member

Excel Ninja

Member

Member

Attachments

Excel Ninja

Attachments

Member

Excel Ninja

Member