Excel - How to count numbers in Brackets

Solved/Closed
YETO_2046 - Updated on May 18, 2020 at 09:08 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 18, 2020 at 11:57 AM
Hello,
I would like to know how to count the number of elements(digits) in the brackets after a word.
exemple
Bimbia(1,2,3,6,7,8). in the Brackets we have 6 numbers
Vivoki(4,5,9). in the Bracket, we have 3 numbers. please which formula to use within excel to find this result?
thankyou
BR


System Configuration: Windows / Chrome 81.0.4044.138
Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 18, 2020 at 11:57 AM
Hi Yeto,

Putting multiple data in single cells makes it hard to process the data. So the first step would be to separate your data.

You can do this manually by selecting your column, use find and replace (Ctrl+H) and replace both "(" and ")" with a ",". Then use the Text delimiter (found on the data ribbon) to separate your data by ",".

OR

You can use the code below, which will do the above steps for you. The code assumes your data is is column A and places the extracted text in column C and the numbers in the columns to the right column C. This will leave column B for the sum function to get the totals you are after.

The code:
Sub RunMe()
Columns(1).Copy Columns(3)
With Columns(3)
    .Replace What:="(", Replacement:=","
    .Replace What:=")", Replacement:=","
    .TextToColumns Destination:=Range("C1"), Comma:=True
End With
End Sub


To adjust the code to your needs:
Code line 2: Columns(1) refers to column A where you source data is.
Code line 2 and 3: Columns(3) refers to column C where your destination data will be placed.
Code line 6: When you change the Columns(3) reference, then also change the column reference here Range("C1").

In case you haven't used VBA codes before:
Open VBA window: Alt + F11
Insert Module: Top menu --> Insert --> Module
Paste code in the big white field.
VBA window can be closed now.
Back at the familiar Excel screen open Macro window: Alt + F8.
Double click RunMe.

Best regards,
Trowa