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. ### IZ2018Member

Messages:
37
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. ### HuiExcel NinjaStaff Member

Messages:
11,690
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_yipExcel Ninja

Messages:
2,105

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:

• ###### SplitSpecifiedData.xlsx
File size:
10.8 KB
Views:
10
IZ2018, GraH - Guido, Hui and 2 others like this.
4. ### bosco_yipExcel Ninja

Messages:
2,105
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

File size:
10.9 KB
Views:
10
5. ### NebuExcel Ninja

Messages:
2,135
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

File size:
15 KB
Views:
2
6. ### IZ2018Member

Messages:
37
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

File size:
10.8 KB
Views:
8
7. ### shrivallabhaExcel Ninja

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

Messages:
37
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. ### NARAYANK991Excel Ninja

Messages:
16,619
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:

• ###### Example 1.xlsx
File size:
11.5 KB
Views:
7
Thomas Kuriakose and bosco_yip like this.
10. ### IZ2018Member

Messages:
37
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. ### NARAYANK991Excel Ninja

Messages:
16,619
Hi ,

You would need exactly 6 columns.

Excel has 16384.

Narayan
12. ### shrivallabhaExcel Ninja

Messages:
1,921
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. ### IZ2018Member

Messages:
37
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. ### ChihiroExcel Ninja

Messages:
5,171
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 - GuidoWell-Known Member

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

Messages:
37
Thank you all, both formulas from above and VBA work great.
17. ### IZ2018Member

Messages:
37
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_yipExcel Ninja

Messages:
2,105
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
19. ### IZ2018Member

Messages:
37
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. ### IZ2018Member

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

File size:
11 KB
Views:
5
21. ### shrivallabhaExcel Ninja

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

See attached demo.

File size:
17 KB
Views:
1
22. ### IZ2018Member

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

23. ### Khalid NGOExcel Ninja

Messages:
1,976
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. ### IZ2018Member

Messages:
37
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