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
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
@@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
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
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.
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.
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
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
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
Unique feature and very useful. Thank you.
Glad you found the video useful 🙂
Macro works like a charm
Thank u Sumit for this great video 📹 😊
Share more excel tips and tricks which is usually used in bpo.
Thank Sir ❤
Hi Sumit...if you could please make few videos on Excel 365 popular formulas with examples please ...
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
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?
How can I send an excel file to you or attach an excel file for you to see?
I will definitely choose substitute function 😅
👍
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.
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.
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.
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