WeThotUWasAToad
asked on
Modify formatting of selected intracellular text
Hello,
Is there a VBA script which will selectively modify the formatting of text within a cell in Excel?
For example, suppose you've got a cell with the following text entry:
The cell itself is formatted to Tahoma/Regular/10/Automati
• the word "brown" has been modified to Tahoma/Bold/10/Brown,
• the word "jumps" has been modified to Tahoma/Regular/14/Automatic, and
• the number "1" has been modified to Tahoma/Superscript/10/Automatic.
With the VBA, is it possible to modify selected parts of the cell based on formatting? For example, suppose you want to change the word brown to green font but leave the remainder of the cell contents unchanged? Obviously that can be done manually, but is there a VBA code that will do it automatically?
Thanks
Is there a VBA script which will selectively modify the formatting of text within a cell in Excel?
For example, suppose you've got a cell with the following text entry:
The cell itself is formatted to Tahoma/Regular/10/Automati
• the word "brown" has been modified to Tahoma/Bold/10/Brown,
• the word "jumps" has been modified to Tahoma/Regular/14/Automatic, and
• the number "1" has been modified to Tahoma/Superscript/10/Automatic.
With the VBA, is it possible to modify selected parts of the cell based on formatting? For example, suppose you want to change the word brown to green font but leave the remainder of the cell contents unchanged? Obviously that can be done manually, but is there a VBA code that will do it automatically?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
gowflow, thanks a bunch for posting the code and for your explanations.
I suspect you may have inadvertently neglected to attach the file you mentioned. No worries though because I have done the same thing hundreds of times (usually in emails) and continue to do it. It was probably just as well though because, as you correctly observed, I am trying to become more familiar with VBA.
Unfortunately, my VBA familiarity is still hugely lacking because I attempted to create the file myself. (Below is a screenshot of the spreadsheet followed by one of your code pasted in the VBA editor.) However, I don't know if the code is inserted correctly and I did not see an "Apply Format" button.
The file is also attached but one other item I should mention is that in looking at the code, I noticed it includes specific words from the text entry (eg brown, jumps, etc). I have, of course, not yet seen the actual effects of the code but I'm wondering if the mechanism of changing intracellular (ie inside-the-cell) formatting is related to or accomplished by recognition of the specific words. If that's the case, then it is certainly a step in the right direction but it would not be as generally useful as I was hoping.
In the first screenshot above, I included the Find and Replace box. We have all used that many times of course but one of its very best features, imo, is the ability to set specific formatting in the "Find what:" box which is then applied as part of the Find filter, and similarly, specify formatting for the "Replace with:" box which is applied to the result.
I don't know if it's even possible but, it would be awesome to have a similar process for doing that with "intracellular" parts without regards to or without changing the actual words.
Thanks again,
Steve
VBA-for-Intracellular-Formatting-ch.xlsm
gowflow
Check out and try it with the attached file I added it to a button Apply Format.
I suspect you may have inadvertently neglected to attach the file you mentioned. No worries though because I have done the same thing hundreds of times (usually in emails) and continue to do it. It was probably just as well though because, as you correctly observed, I am trying to become more familiar with VBA.
Unfortunately, my VBA familiarity is still hugely lacking because I attempted to create the file myself. (Below is a screenshot of the spreadsheet followed by one of your code pasted in the VBA editor.) However, I don't know if the code is inserted correctly and I did not see an "Apply Format" button.
The file is also attached but one other item I should mention is that in looking at the code, I noticed it includes specific words from the text entry (eg brown, jumps, etc). I have, of course, not yet seen the actual effects of the code but I'm wondering if the mechanism of changing intracellular (ie inside-the-cell) formatting is related to or accomplished by recognition of the specific words. If that's the case, then it is certainly a step in the right direction but it would not be as generally useful as I was hoping.
In the first screenshot above, I included the Find and Replace box. We have all used that many times of course but one of its very best features, imo, is the ability to set specific formatting in the "Find what:" box which is then applied as part of the Find filter, and similarly, specify formatting for the "Replace with:" box which is applied to the result.
I don't know if it's even possible but, it would be awesome to have a similar process for doing that with "intracellular" parts without regards to or without changing the actual words.
Thanks again,
Steve
VBA-for-Intracellular-Formatting-ch.xlsm
Sorry your absolutely correct for the file. I did not read all your comments here is the file check it and if still issues let me know I will read thru as now a bit in a hurry.
Sorry again for this overlooking !!! :(
gowflow
Diff_Format_In_one_Cell-V01.xlsm
Sorry again for this overlooking !!! :(
gowflow
Diff_Format_In_one_Cell-V01.xlsm
ASKER
gowflow,
Yes, the attachment works great! I've never known this type of "intracellular" reformatting was even possible so I'm delighted to see it.
Also, it answers my question from above, (ie that the code clearly operates by identifying specific text since the original text in cell A2 is of uniform formatting).
As I've thought about it, this approach could actually be very helpful if the specific text in your VBA code could be replaced with variables. In other words, suppose the spreadsheet includes additional columns that specify which elements of the initial entry should be reformatted in some defined way.
No need to comment on that in this thread because the code you have already provided satisfies my question in the OP. Therefore, I will be closing this post in the next day or so but I plan to post a follow-up question (in a new thread) tomorrow and will post the link (as soon as I have it) in a new comment below.
Many thanks,
Steve
Yes, the attachment works great! I've never known this type of "intracellular" reformatting was even possible so I'm delighted to see it.
Also, it answers my question from above, (ie that the code clearly operates by identifying specific text since the original text in cell A2 is of uniform formatting).
As I've thought about it, this approach could actually be very helpful if the specific text in your VBA code could be replaced with variables. In other words, suppose the spreadsheet includes additional columns that specify which elements of the initial entry should be reformatted in some defined way.
No need to comment on that in this thread because the code you have already provided satisfies my question in the OP. Therefore, I will be closing this post in the next day or so but I plan to post a follow-up question (in a new thread) tomorrow and will post the link (as soon as I have it) in a new comment below.
Many thanks,
Steve
ok great
waiting for your link and for sure we can build whatever we want in extra column but remember 1 thing that is very important.
At the end you can put extra columns and put variables etc... but then you will think I need to select also the color then the font and also here put extra columns ... and bold yes no ... and superscript etc.... you can automate as much as you want
BUT BUT
At the end if you spend your time filling columns and coding etc... to get an output that is not worth all the trouble then the heck with all this just do it manual.
I am trying to put you on the right track as we have sometime tendency to start building on and on ... we have to slow down and stop and think is it simple can it be applied easily is is easy to input the details ...
Just a thought before you jump on a new train.
For me it is all my field so I can code whatever but I always keep the common sense in mind and try never to go over board.
gowflow
waiting for your link and for sure we can build whatever we want in extra column but remember 1 thing that is very important.
At the end you can put extra columns and put variables etc... but then you will think I need to select also the color then the font and also here put extra columns ... and bold yes no ... and superscript etc.... you can automate as much as you want
BUT BUT
At the end if you spend your time filling columns and coding etc... to get an output that is not worth all the trouble then the heck with all this just do it manual.
I am trying to put you on the right track as we have sometime tendency to start building on and on ... we have to slow down and stop and think is it simple can it be applied easily is is easy to input the details ...
Just a thought before you jump on a new train.
For me it is all my field so I can code whatever but I always keep the common sense in mind and try never to go over board.
gowflow
ASKER
gowflow,
That is very wise and appropriate advice. I have definitely found myself in that situation before when, after completing some project or other, I've discovered that I would have been better off and spent less time just pounding it out manually rather than try to automate it. I guess the trick is having the discernment upfront to know the best direction to go. :P
One thing I might mention fyi, is that due to a spinal cord injury many years ago, I have no use of my fingers (although I do have some movement in my arms). Fortunately, I am able to use a PC with the help of some adaptive hardware & software. However, manual data entry for me is much slower than normal so I tend to lean in the direction of automation to a greater degree than would otherwise be the case.
With regards to the specific functionality we've been addressing in this thread, there have been a number of times in the past — and I'm sure there will be more the future — in which having the ability to modify intracellular elements of a cell in Excel would be of enormous benefit. Therefore, I am anxious to discover where this process will lead.
Thanks,
Steve
PS I anticipate having the new thread posted within an hour or so.
That is very wise and appropriate advice. I have definitely found myself in that situation before when, after completing some project or other, I've discovered that I would have been better off and spent less time just pounding it out manually rather than try to automate it. I guess the trick is having the discernment upfront to know the best direction to go. :P
One thing I might mention fyi, is that due to a spinal cord injury many years ago, I have no use of my fingers (although I do have some movement in my arms). Fortunately, I am able to use a PC with the help of some adaptive hardware & software. However, manual data entry for me is much slower than normal so I tend to lean in the direction of automation to a greater degree than would otherwise be the case.
With regards to the specific functionality we've been addressing in this thread, there have been a number of times in the past — and I'm sure there will be more the future — in which having the ability to modify intracellular elements of a cell in Excel would be of enormous benefit. Therefore, I am anxious to discover where this process will lead.
Thanks,
Steve
PS I anticipate having the new thread posted within an hour or so.
No problem I will be here to assist you best possible and it will be my pleasure.
I am very sorry to learn about your accident and salute your moral to stay busy and in quest for learning which clearly indicate high spirit high belief and an exceptional attitude which we can only but congratulate.
I will keep what you said in mind when you post your question to see how best to summarize the options to offer maximum flexibility and at the same time try as much as possible keeping it simple
Regards to you
gowflow
I am very sorry to learn about your accident and salute your moral to stay busy and in quest for learning which clearly indicate high spirit high belief and an exceptional attitude which we can only but congratulate.
I will keep what you said in mind when you post your question to see how best to summarize the options to offer maximum flexibility and at the same time try as much as possible keeping it simple
Regards to you
gowflow
ASKER
Here is the link for the follow-up thread:
Modify formatting of selected intracellular text strings in Excel
Modify formatting of selected intracellular text strings in Excel
ok I will look at it, shouldn't you close this one first ?
gowlfow
gowlfow
ASKER
Great solution. Thanks
ASKER