Remove text from string between parenthesis leaving only numbers

[zipotontic]

Hello!
I have an inventory list with products of various units of measure (box, each, spool, etc). If it is more than 1, the total number of units is included in parenthesis at the end of the item name. So, for example, if it is a spool of 100 feet, it will show (100FT) or, if it is a box of 50, it will show (50/bx), etc.

What I am trying to achieve is a column that lists only the numeric values found between the parenthesis.

So far I have written two different formulas that extract the string between the parenthesis. However, there are issues with each. Formula 1 extracts the entire string in between the parenthesis, I cannot figure out how to modify/add to this formula so that it will remove the text and leave only the number. Formula 2 is long and clunky but it does the trick more or less. The issue is it creates a lot of work for me because I have to make sure I scan an entire list of thousands of products to be sure I have included all units of measure (so far I have only found box, spool, each). Also, occasionally there will be an item with some other random bit of information in parenthesis not related to the unit of measure and my formula will end up displaying that.

Formula #1:
=IFERROR(MID(LEFT(A2,FIND(")",A2)-1),FIND("(",A2)+1,LEN(A2)),"")

Formula #2:
IF(D16="SPOOL",MID(LEFT(A16,FIND("FT)",A16)-1),FIND("(",A16)+1,LEN(A16)),IF(D16="BOX",MID(LEFT(A16,FIND("/BX)",A16)-1),FIND("(",A16)+1,LEN(A16)),IF((ISNUMBER(SEARCH(")",A16))),RIGHT(TEXTBEFORE(A16, ")"),3),"")))

Thanks so much for the help!

 

[Micron]

Glad you stepped in with the RegEx solution Peter, because I've tried to learn it but it just doesn't seem to want to sink in. I can usually get a solution but it usually takes more code without RegEx.

 

[zipotontic]

Welcome to the MrExcel board!

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers.


Not sure how many thousands but you may need to be a little wary of using the suggested formula from post #5 since INDIRECT is a volatile function and may slow your sheet performance. However, @johnnyL couldn't that volatile structure be replace with a SEQUENCE function since it seems the OP has a version with that available?

Another possible negative to the formula would be if data like I have in row 12 below is possible as the formula would not pick up that number.


I do have a vba suggestion. I have assumed that the data is in column A and column B is available for the results.
Test with a copy of your workbook.

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy (you can use the icon at the top-right of the forum code window) and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the GetNumbers macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Sub GetNumbers()
  Dim RX As Object
  Dim a As Variant, b As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(\([^\d\)]*)(\d+)"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      If RX.test(a(i, 1)) Then b(i, 1) = RX.Execute(a(i, 1))(0).SubMatches(1)
    Next i
    .Offset(, 1).Value = b
  End With
End Sub

My sample data (thanks to @johnnyL for the XL2BB sample data) & vba results:

zipotontic.xlsm
	A	B
1
2	#10 CABLE TIE MOUNT (100)	100
3	Fuse Holder ATO/ATC Waterproof
4	1/4IN BLK FIRE RTRD SPLIT LOOM (50/BX)	50
5	1IN BLK FIRE RTRD SPLIT LOOM (50/BX)	50
6	4IN NYLON CABLE TIE BLACK (100)	100
7	5.7IN NYLON CABLE TIE BLACK (100)	100
8	8IN NYLON CABLE TIE BLACK (100)	100
9	11.8IN NYLON CABLE TIE BLACK (100)	100
10	1/8IN BLK HEATSHRINK 48IN
11	ROTATING CABLECAP INSULATOR (PR)
12	ROTATING CABLECAP INSULATOR (PR) (EACH 50)	50
13	14/2 RED/YEL RND SAFETY WIRE (100FT)	100
14	12/2 RED/YEL RND SAFETY WIRE (100FT)	100
Sheet1

Wow!!! This is incredible, thank you so much!!! All of you guys are so smart, it's mind blowing! How kind of you to write such a detailed explanation and provide the function, thank you! It works perfectly and I figured out how to change the column references in case we need to modify the spreadsheet with more columns in the future.

Thank you very, very much Peter!!

Where could I learn this? I would love to take some courses.

Thank you again! Cheers

 

[zipotontic]

Welcome to the MrExcel board!

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers.


Not sure how many thousands but you may need to be a little wary of using the suggested formula from post #5 since INDIRECT is a volatile function and may slow your sheet performance. However, @johnnyL couldn't that volatile structure be replace with a SEQUENCE function since it seems the OP has a version with that available?

Another possible negative to the formula would be if data like I have in row 12 below is possible as the formula would not pick up that number.


I do have a vba suggestion. I have assumed that the data is in column A and column B is available for the results.
Test with a copy of your workbook.

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy (you can use the icon at the top-right of the forum code window) and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the GetNumbers macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Sub GetNumbers()
  Dim RX As Object
  Dim a As Variant, b As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(\([^\d\)]*)(\d+)"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      If RX.test(a(i, 1)) Then b(i, 1) = RX.Execute(a(i, 1))(0).SubMatches(1)
    Next i
    .Offset(, 1).Value = b
  End With
End Sub

My sample data (thanks to @johnnyL for the XL2BB sample data) & vba results:

zipotontic.xlsm
	A	B
1
2	#10 CABLE TIE MOUNT (100)	100
3	Fuse Holder ATO/ATC Waterproof
4	1/4IN BLK FIRE RTRD SPLIT LOOM (50/BX)	50
5	1IN BLK FIRE RTRD SPLIT LOOM (50/BX)	50
6	4IN NYLON CABLE TIE BLACK (100)	100
7	5.7IN NYLON CABLE TIE BLACK (100)	100
8	8IN NYLON CABLE TIE BLACK (100)	100
9	11.8IN NYLON CABLE TIE BLACK (100)	100
10	1/8IN BLK HEATSHRINK 48IN
11	ROTATING CABLECAP INSULATOR (PR)
12	ROTATING CABLECAP INSULATOR (PR) (EACH 50)	50
13	14/2 RED/YEL RND SAFETY WIRE (100FT)	100
14	12/2 RED/YEL RND SAFETY WIRE (100FT)	100
Sheet1

Oh, also I will update my account details as you suggested. And I did try the XL2BB but I kept getting all sorts of privacy blocking errors and never could get it to work. I will practice that so next time I can provide a better example.

Thank you so much again!!

 

[zipotontic]

You are exactly right.

I stink at formulas so I just put together something that worked for the data provided. I totally missed the 'thousands' reference in the OP.

Thank you so much JohnnyL! Really appreciate your help and with the sample sheet I am using it works perfectly! Since you guys both agree it might be problematic with my actual inventory list of thousands, I have implemented the VB function provided by Peter_SSs and it works beautifully so I will probably use that. Thank you for taking the time to respond to my post and provide a formula! You guys are super great!

 

[johnnyL]

Glad we could help.

 

[zipotontic]

With the macro of course.


.. otherwise possibly this I guess?

zipotontic.xlsm
	A				E
1
2	#10 CABLE TIE MOUNT (100)				100
3	Fuse Holder ATO/ATC Waterproof
9	11.8IN NYLON CABLE TIE BLACK (100)				100
10	1/8IN BLK HEATSHRINK 48IN
11	ROTATING CABLECAP INSULATOR (PR)
12	ROTATING CABLECAP INSULATOR (PR) (EACH 50)				50
14	12/2 RED/YEL RND SAFETY WIRE (100FT)				100
15
16	ROTATING CABLECAP INSULATOR (EACH 50) (PR)				50
17	(ROTATING) (CABLECAP) INSULATOR 45 (EACH 50) (PR)				50
Sheet2

Cell Formulas
Range	Formula
E2:E3,E9:E12,E14:E17	E2	=LET(ch,MID(A2&"|",SEQUENCE(LEN(A2)+1),1),IFNA(TEXTBEFORE(TEXTAFTER(SUBSTITUTE(CONCAT(IF(ch="(",ch,IF(ch=")",ch,IFERROR(ch+0,"")))),"()",""),"("),")")+0,""))

Oh wow....you definitely seem to be the Master. Thank you again!! Both your VB function and this formula work absolutely perfectly. I think though, as you were so kind to provide it, I will use the VB alternative as it seems to be the best option for handling such a large list. Also, it makes me feel cool. Thank you so much again Peter_SSs, super grateful for your time and help!

 

[Peter_SSs]

Thank you very, very much Peter!!

You are very welcome.

Where could I learn this?

I learned most of what I know about regular expressions by studying threads about them in this forum. I did also buy an earlier version of this book by Ben Forta.

Oh, also I will update my account details as you suggested.

Don't forget that.

And I did try the XL2BB but I kept getting all sorts of privacy blocking errors and never could get it to work.

See if this helps: Xl2bb is disabled
.. or there could be other useful threads in that About This Board forum.

Also, it makes me feel cool.

Very important point!!