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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 18, 2020 at 11:57 AM
Related:
- When a number value is entered in brackets in an excel cell it means
- Square brackets mac - Guide
- Number to words in excel - Guide
- How to enter @ in laptop - Guide
- Gif in excel - Guide
- Marksheet in excel - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
May 18, 2020 at 11:57 AM
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:
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
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
