Retrieve first number from parentheses

[tao94539]

Hello

Anyone can help se formula to search inside ( ), and pick the first number below?

so I need to pick like 291188
291187
******
391629
396724
*****
Thanks,
James


• GoVino Red Wine Glass 12/2 Pack 16oz Top Rack Dishwasher Safe (291188-CA)
• GoVino White Wine Glass 12/2 Pack 12oz Top Rack Dishwasher Safe (291187-CA)
• GoVino Flute 12/2 Pack 8oz Top Rack Dishwasher Safe (291190-CA)
• GoVino Red Wine Glass 12/4 Pack 16oz Top Rack Dishwasher Safe (291183-CA)
• GoVino Red Wine Glass 72/16oz Top Rack Dishwasher Safe (291166-CA)
• GoVino White Wine Glass 12/4 Pack 12oz Handwash (291193-CA)
• GoVino Flute 12/4 Pack 8oz Top Rack Dishwasher Safe (291189-CA)
• GoVino Decanter 12/32oz Top Rack Dishwasher Safe (291186-CA)
• GoVino White Wine Glass 72/12oz Top Rack Dishwasher Safe (291179-CA)
• GoVino Red Wine Glass 12/4 Pack 16oz Handwash (291191-CA)
• GoVino White Wine Glass 12/4 Pack 12oz Top Rack Dishwasher Safe (291192-CA)
• Rhino 7.75 Black Unwrapped Paper Straw 6mm (391618-CA)
• Rhino 6 Black Cocktail Paper Straw 8mm (391621-CA)
• Rhino 7.75 White Unwrapped Paper Straw 6mm (391620-CA)
• Rhino 5.5 Black Unwrapped Paper Straw 6mm (396723-CA)
• Rhino 7.75 Black Wrapped Paper Straw 6mm (391619-CA)
• Rhino 7.75 White Wrapped Paper Straw 6mm (391629-CA)
• Rhino 5.5 White Unwrapped Paper Straw 6mm (396724-CA)

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[AliGW]

That's better.

Now, explain PROPERLY what you are trying to do.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Rick Rothstein]

Give this formula a try...

Please Login or Register  to view this content.

Note: This formula assumes the maximum number of characters you will ever have within a single cell is 300.

[AliGW]

Alternatively:

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("(",A1)+1,99),"-",REPT(" ",99)),15))

[MrShorty]

1) Your examples all show one opening parenthesis in the text string. The FIND() function (https://support.office.com/en-us/art...3-d0d89b0a0628 ) can find the open parenthesis and tell you where it is FIND("(",text)
2) Your examples all show that the number you want is the next 6 characters of the text string. The MID() function (https://support.office.com/en-us/art...8-4ecb12433028 ) can extract a substring from a larger string, using the result of the FIND() function to tell it where to start. =MID(text,FIND(...)+1,6)
3) the MID() function returns a text string. if you need the number as a number and not text, then you can use the VALUE() function or other strategy to convert the number as text to a real number.

Assuming that there will always be only one set of parentheses and the number is always the 6 characters immediately after the open parenthesis, that should work to extract the number.

[Rick Rothstein]

One more possible alternative. If all your numbers are 6 digits long like in your example, then this will work as well...

Please Login or Register  to view this content.

[Rick Rothstein]

And, if what follows your numbers is always a dash followed by two characters (and a closing parenthesis), then you could even use this...

Please Login or Register  to view this content.

[tao94539]

Thanks everyone, all solutions work, but I need to dig to understand what is going on

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.