Hello, do you know about some simple VBA command, to remove everything between to characters including these characters? In this case it would be [random text], possibly also with space before [?
I need to loop this through all items in column B, there might be 5 items, there might be 150 items, but always in column B.
In pictures you can see example of before and after macro would be applied. Thank you for responses.
Clipboard01.jpg
Clipboard02.jpg
Last edited by Hecky83150; 03-13-2020 at 06:37 AM.
Please attach an EXCEL sample file ...!
- Battle without fear gives no glory - Just try
I don't know why no formula but if you want to:
Please Login or Register to view this content.
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
I'm sorry I'm getting error (javascript) fails, when I click to attachment nothing happens.Originally Posted by PCI
Try a bit longer version (but also working with something like blahblah [123] blahblah
PS as for attachment - have you followed this suggestion:Please Login or Register to view this content.
Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).
1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).
3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!
To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.
Best Regards,
Kaper
Thank you, I have managed to add attachment example now, and your code works well. I'm sorry I've tried it wrong way to put there example.Try a bit longer version (but also working with something like blahblah [123] blahblah
PS as for attachment - have you followed this suggestion:Please Login or Register to view this content.
Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).
1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).
3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!
To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.
Try
Please Login or Register to view this content.
Probablybut as for dealing with different number (or non-present) spaces, cell-by-cell approach will be needed anywayPlease Login or Register to view this content.
Please Login or Register to view this content.
You are welcome and thanks for the rep.
Re: VBA to remove text between to character (including those characters)
Hi,
on a cell-by-cell approach Split VBA function is the easy way to go …
For the fun another way ... Marc, beat me one click
Please Login or Register to view this content.
I'd still add trunc to it like:
Please Login or Register to view this content.
Yes that was concerning the last space, I did not want to care and it is nicer than the TRIM statement.I'd still add trunc to it like
Thx
Re: VBA to remove text between to character (including those characters)
Salut PCI !
Well, according to the attachment, it needs a single Split …
@ Kaper
Why would you use Trunc ? Afaik Trunc is used to get the integer part of a number (like VBA Fix) but we're dealing with Text strings here ?
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
to follow Kaper's commentvRTRIM statement is a possibility
Please Login or Register to view this content.
Re: VBA to remove text between to character (including those characters)
As it needs only a single Split so On Error codeline is useless, according to the attachment …
Well it is in case there is some records with out "["On Error codeline is useless
Can you show what will be a solution with a single split, just to understand and learn
According to the Excel VBA main coding rule I would first go like in post #7 …
But for some reason if really a 'cell-by-cell approach' is needed
according to the attachment the Split VBA function is obviously the easy way to go :
PHP Code:Sub Demo1()
Dim V, R&
With [A1].CurrentRegion.Columns(2)
V = .Value2
For R = 1 To UBound(V): V(R, 1) = Split(V(R, 1), " [")(0): Next
.Value2 = V
End With
End Sub
Well that's very not a concern with the index #0 …
Assuming you only had to handle one or no space possibilities, you could simply do two global replacements instead iterating each cell individually...Probablybut as for dealing with different number (or non-present) spaces, cell-by-cell approach will be needed anywayPlease Login or Register to view this content.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks