Excel - How to count numbers in Brackets [Solved]

Report
-
Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
-
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

1 reply

Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
444
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
2
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month