Hi,
I'm looking to replace the bold words in a piece of text, with the same words but surrounded by brackets:
From:
This is an example sentance.
To:
This is an {example} sentance.
Or:
This is an {example} sentance.
Any help would be much appreciated.
Try with if your data in "A2"
Formula:
Please Login or Register to view this content.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Hi,
Here's a sheet double click event that will do what you ask.
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
This is a more "mechanical" approach:
As in Richard Buttrey solution, the change is triggered by a double click on the cell containing the text
Please Login or Register to view this content.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
Many thanks all, these have the desired effect - however, I'm looking to do this as a batch operation, rather than manually. How would I go about converting several thousand lines of text in this way? Apologies for not specifying in my OP.
Oh dear!
I would not recommend using my method with thousands of rows. You may experience two birthdays waiting for an answer!
Last edited by kev_; 04-18-2017 at 11:29 PM.
Give this a try EDIT: after further thought - posts #8 & #9 probably yield a better solution for you
- in testing it took approx. 4 seconds per 1000 cells
- suggest you test it on 100 cells first
- if you run it on full data set close all other applications
- it checks every cell in allotted range
- define your range as tightly as possible eg Range("A:Z") would take forever as VBA checks more than 26million cells
- amend sheet and range details
This line avoids empty cells and those containing formulas from being processed:
Place this in a General Module:Please Login or Register to view this content.
To reduce the number of cells being checked:Please Login or Register to view this content.
If there are discrete blocks where the text is located, range could be defined with the UNION function:
Please Login or Register to view this content.
Last edited by kev_; 04-19-2017 at 01:48 AM.
A late thought.
This could probably be a better way to set your range (based on cells containing text only):
Test it standalone first with the message box to see what it yieldsPlease Login or Register to view this content.
IF condition can then be removed from the VBA and the code (to be placed in a general module) becomes:
Please Login or Register to view this content.
Last edited by kev_; 04-19-2017 at 01:50 AM.
And combining this technique:
With the UNION function to give:Please Login or Register to view this content.
results in only cells containing constant text in those ranges being checkedPlease Login or Register to view this content.
Apologies for the rather scrappy build up - I forgot all about the rather useful attribute xlTextValues
Last edited by kev_; 04-19-2017 at 01:53 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks