4 Easy Ways to Remove Parentheses in Excel
ฝัง
- เผยแพร่เมื่อ 30 ก.ค. 2024
- ☕ If you find my Excel videos useful and would like to support me, you can buy me a coffee - www.buymeacoffee.com/SumitB
In this video, I will show you four simple methods you can use to quickly remove parentheses in Excel.
The following methods are covered in this video:
- Using Flash Fill
- Using Find and Replace
- Using the SUBSTITUTE Function
- Using VBA Macro
You can download the example file from here - www.dropbox.com/s/4zjyqelp3th...
You can also read the tutorial here - trumpexcel.com/remove-parenth...
00:00 Intro
00:18 Remove Parentheses Using Flash Fill
02:12 Remove Parentheses Using Find and Replace
03:28 Remove Parentheses Using the SUBSTITUTE Function
05:34 Remove Parentheses Using VBA Macro
------------------------------------------------------------------------------------------------
'Code developed by Sumit Bansal from trumpexcel.com
Sub RemoveParentheses()
Dim rng As Range
Dim cell As Range
' Set the current selection as the range to be used
Set rng = Selection
' Loop through each cell in the selection
For Each cell In rng
' Remove parentheses
cell.Value = Replace(cell.Value, "(", "")
cell.Value = Replace(cell.Value, ")", "")
Next cell
End Sub
----------------------------------------------------------------------------------------------------------
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-c...
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
✅ Best Excel Books: trumpexcel.com/best-excel-books/
Subscribe to get awesome Excel Tips every week: th-cam.com/users/trumpexc...
#Excel #ExcelTips #ExcelTutorial
If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos.
Also, I have made all of my Excel courses available for free. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
Macro works like a charm
Unique feature and very useful. Thank you.
Glad you found the video useful 🙂
Thank u Sumit for this great video 📹 😊
Thank Sir ❤
Share more excel tips and tricks which is usually used in bpo.
I will definitely choose substitute function 😅
Hi Sumit...if you could please make few videos on Excel 365 popular formulas with examples please ...
👍
Hi, i am trying to create time in lieu spreadshit to capture time work, time accrued and time taken, just like with leave tracker you created. If you could guide me on this it would be greatly appreciated.
Good one... 👏...
1:53 Seems data in A7 do not match with data in B7
Yeah, I didn't notice it. One of the cases where Flash fill wasn't able to identify the pattern right, so maybe give more entries manually and then use Flash Fill
@@trumpexcel what 😲 could be causing the problem 🤷♀️... It's not that parenthesis are not being removed... 😵 But new data is being added 😨
@@lazyreviewssupport9811 yeah, this is weird. I have seen it give wrong results, but adding stuff on its own is new. I am sure it has to do with how it identifies the pattern and decides what to remove and what to keep. I am away from my system, but will come back and try to decode why this is happening
How can I send an excel file to you or attach an excel file for you to see?
Useful feature but please be noted while applying flash fill method, what happens in 7th row is that "New" appears twice. Could you please give the reason for that?
I believe this is happening because in all the other cases there is only one word within parentheses, but in A7 there are two words within parentheses so Flash fill gets confused with the pattern. A good example of why flash fill could sometimes giving incorrect results
@@trumpexcel Would it happen if it was written as New-York instead of New York?
I'm unable to download material in your website bro, please let me know, how to get material
Can you please let me know what are you not able to download? Will fix it.
I have a table with customer name, sales stage, amount, Year and Month of each sales stages. There are 4 types of sales stages like lead, opportunity, demo and proposal. I keep on changing the sales stages based on the latest sales update from customer. I want to create a sales pipeline dashboard in excel sheet. I want t dashboard with chart of sales stages and slicer of month and year. I want the chart of sales stage should be changed based on selection of month and year.
For an example- I have created a lead in August 2023 then change the stage to demo in September 2023 and then again change the stage to proposal in October 2023. Now if Select September in dashboard slicer the chart should show Lead-0, demo-1 and proposal- 0. Similarly when I select October then chart should show Lead-0, demo-0 and proposal- 1.
Please help to create similar dashboard in excel sheet.
In VBA what if i need to remove even text in parentheses?
Try this:
Sub RemoveParentheses()
Dim rng As Range
Dim cell As Range
Set rng = Selection 'change this to your range
For Each cell In rng
cell.Value = RemoveParenthesesInString(cell.Value)
Next cell
End Sub
Function RemoveParenthesesInString(s As String) As String
Dim openParen As Integer
Dim closeParen As Integer
openParen = InStr(s, "(")
closeParen = InStr(s, ")")
While openParen > 0 And closeParen > 0
s = Left(s, openParen - 1) & Mid(s, closeParen + 1, Len(s))
openParen = InStr(s, "(")
closeParen = InStr(s, ")")
Wend
RemoveParenthesesInString = s
End Function
The Easiest way : Flash Fill.
But, in the 7th Row: NEW is repeated TWO TIMES !! What could be the reason for that ?
"Promosm" 🤦
Another great video that provides very useful and practical tips for cleaning data in Excel! Thanks very much for posting.
Another option would be to use Power Query to easily remove the unwanted characters. As far as I can tell, however, the Replace Values feature works with one character at a time, similar to VBA's Replace() function. I was wondering if it were possible to use a custom M language function that accepts one or more characters to remove from a given string. Below is my attempt at addressing this issue. Since I have only been studying Power Query for three weeks, I assume that what I wrote is pretty awful and inefficient. But it does seem to work, based on my very limited testing. Thank you kindly.
===============================================
// fxRemoveChars()
// Remove one or more characters from the input string and
// return the modified string.
// Text.SplitAny() will break up the input string on all of
// the specified delimiters, which are the characters we want to remove.
// Then reassemble the list object returned by Text.SplitAny()
// into a text string and return its value.
(input as text, remove_chars as text) =>
let
split_input = if input null
then Text.SplitAny(input, remove_chars)
else null,
output = if split_input null
then Text.Combine(split_input, null)
else null
in
output