Link to home
Start Free TrialLog in
Avatar of WeThotUWasAToad
WeThotUWasAToad

asked on

Selective inside-a-cell reformatting in Excel

Hello,

This is another in a series of questions I have posted recently to come up with Excel VBA code that will selectively reformat a specified string (or strings) of text within a cell, but not the entire cell.

For example, suppose you wanted to reformat a sentence in cell B2 to that shown in D2:

User generated image
Note that the particular formats shown in this screenshot are arbitrary as the main objective is to have a code which can apply any desired formatting.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WeThotUWasAToad
WeThotUWasAToad

ASKER

Thanks a ton gowflow. And thanks for including the attached file.

I really like that your code can determine formatting from a single sample cell. That is much better than for the user to have to enter the name of the desired font, the font size, a color hex code, etc.

I have a small change and a medium change to suggest, both of which I will include in this post. I will then address a large and more significant change in the next post.


Small Issue

The reformatting confirmation pop-up box shown here seems to add an extra unneeded step so I think it can be eliminated.

User generated image

Medium Issue

I believe it would be best to confine each column to a single format. The reason is that having multiple different formatting options scattered around seems a bit confusing (shown here from your attached file).

User generated image
Furthermore, from a practical standpoint, I think it is likely that:

        a) in most cases, the user will be making only a small number (maybe 1-3) of formatting changes, and
        b) if many text samples are to be reformatted, most (if not all) will involve the same formatting choices.

This single-format-per-column approach could be accomplished by designating a single heading-type row (near the top) in which the formatting for each full column could be entered as shown here (note that I have replaced the headings ["Var 1", "Var 2"...etc] with ["String 1", "String 2"...etc]):

User generated image
More comments to follow...
Major Issue

One thing I noticed with the current code is that it works fine as long as a text string to be reformatted is not repeated or does not appear more than once in the original. However, if the text string is repeated, then ambiguity arises regarding which string is meant to be changed. In fact, it seems that the formatting changes are always applied to the first occurrence (and only the first occurrence) of a matching text string. As a result, it seems that there is no method for reformatting any other instances of the same string.

To illustrate this behavior, the next screenshot is from the file you attached and includes your three "Original Text" samples along with three more I added. (Please click the image to see it more clearly.)

User generated image
Note that in sample #4, the blue text string, "Thanks a ton!" (cell D9) works great and is applied as it should be in the "Formatted Text" column (cell C9).

However, in sample #5, when the same blue text string, "Thanks a ton!" (cell D11) is accompanied by a second text string consisting of the word "Thanks" in a small red font (cell E11), a problem appears in the result (cell D11). Because the word, "Thanks" occurs twice in the original (cell B11), it's likely that a hypothetical user intended to apply the red formatting to the second "Thanks" rather than re-changing the first "Thanks". In this sample, I could again not find any method to modify the formatting of the second "Thanks".

A similar situation is shown in sample #6. In this case, the Original Text is exactly the same as #1 with one exception, namely, that the footnote reference at the end of the sentence is the letter "a" rather than the number "1". Thus, in place of the small superscript formatting indicator "1" (cell G3), sample #6 requires the indicator "a" (cell D13). But as before, the code applies the formatting change to the first matching string (ie "a" in the word "lazy") rather than to the final "a" as intended.

Solution

To alleviate this issue, I believe the best solution is to define a text string to be reformatted, not by the string of characters it contains — which as we've seen may recur — but by numerical values representing that string's position in the original text. Doing so will provide the code with explicit and unambiguous information for where the reformatting is to be applied. This method can still provide the user with confirmation of the character string to be changed (to be addressed in my next thread) but more importantly, it will always return the intended result.

In a previous message you warned that using numerical values to define text strings, "somehow will require [me] to count and recount .... and could be a pain."

I really appreciate that warning and the last thing I want is additional pain. For that reason, I have spent some time investigating different methods for obtaining and entering numeric values as described above. However, because that is a different topic than this thread, I will shortly post it as a new question in a new thread.

Thanks a bunch for all the effort and assistance. I truly appreciate it and I am excited that this is looking like it's doable.

Steve
ok let me start answering

1) the easy stuff remove the message box no problem already done here.
2) To apply format per column. Well I have designed it to be extra flexible and to the will of the user and is totally not binding now if you find it somewhat complicated then no problem we can modify it to only act on row2 formatting (for me it is not a problem to cater for this)

Just confirm so I do these 2.
gowflow
As for the Major Issue as you call it, I undertand the challenge and while I am thinking of a workaround, it seems that you are the type to analyse ... post the problem and suggest your solution not even wanting to hear if a workaround is possible.

Well I believe I came up with this code that is a first trial and for sure there is room for adaptation but let's agree on a mechanism
for sure I appreciate all input in providing solutions but also appreciate the problem being exposed as you very well did and for me a chance to seek to a solution for sure unless you don't like the proposed solution at the first place.

While I was designing the format per column .... I started reading your Major Issue then I stopped as fell we need to communicate further.

Also I am not used to running multiple horses at the same time as if you post an other question on numerals and expect me to dig in (for sure unless this is already taken care of) then I prefer if you want that we coordinate somehow.

Anyway, will wait to hear your comments.

For the next or multiple occurences I think I have a way but will leave you to address these first.
gowflow
Any way, not to be complaining and wyning just sake

Try this new version that, although it has in row 2 the formatting you requested, it is not activated it is only still the macro still behave like initially designed with a small twist taking in consideration your comments in the Major Issue !!!

Just run the current strings asis and notice the behavior right from the first sentence where previously it capitalized the first dog and now what is happening.

Basically this new modified version will work sequentially on the strings inputed and look for the next one just after ...

Let me know your comments.... before jumping the gun !!! :) :) LOL

gowflow
Format-Inside-V02.xlsm
Same version as before no change in VBA

Look at row 7 for a more complex thing that is well addressed.
gowflow
Format-Inside-V02.xlsm
As always gowflow, thank you very much for the code and your comments.

I have carefully read your recent posts and spent quite a bit of time with your attached file and both include some items I want to address — which I will do in a subsequent post. However, the main thing I want to convey at this point is that I apologize if my communication or my posts or how things have progressed has been less than optimal. The truth is that I really appreciate your assistance and hope for it to consume as little of your time as possible and certainly not create stress, etc.

Having said that, my main hope in this post is to communicate just a single question which I believe, once addressed, will result in a usable and possibly finished code.

The question is this:

Can a code be written which, using only the information shown in the following screenshot, produces the Formatted Text results in column O?

User generated image
The "information" is of three types:

        1) the original text in column B
        2) the formatting in row 4 for each "String" column
        3) the two numeric values for each formatting change

By the way, the file is attached and I think the two biggest differences between it and previous files are that:

        • this file purposely does not display the string to be changed until it appears in the Formatted Text, and
        • this file purposely disregards anything about how the two numeric values (in #3 above) are obtained.

As you know, I am very much a novice when it comes to VBA. However, in all the code I have been viewing recently, I have not seen any that seems to accomplish what is described here (ie making the formatting changes based only on numeric values).

The process I'm asking about is based on the use of the MID() function which I have mentioned before. And the good news is that, as I discovered on this website recently, the MID() function is usable, not only in a worksheet (WS) formula, but in VBA as well.

The syntax for the MID() function — which I realize you already know — is as follows:

        MID( text, start_position, number_of_characters )

...or abbreviated:

        MID( text, start, chars )


So, referring to the first formatting change in the first sample shown in the screenshot, the three arguments are:

        text = B7
        start = D7  (ie 5)
        chars = E7  (ie 5)

or, in other words, the string to be reformatted (to the blue Comic Sans MS font because the start & chars values reside in the String 1 column) starts with the 5th character ("q") in the Original Text and includes a total of 5 characters ("quick").

Similarly, the three arguments for the string (which is to be changed to the large green Stencil font) in the 2nd sample are:

        text = B10
        start = H10 (ie 32)
        chars = I10  (ie 5)

So being in the String 3 column specifies the new format, [text = B10] identifies the 2nd ("Lorem ipsum...") original, [start = 32] means that the string begins with the 32nd character (ie the letter "u" in the word "ipsum"), and [chars = 5] indicates that the string has 5 characters: "um do".

Note that the order (going from left  to right  in the chart) of strings to be changed (for a given sample) is determined only by which new format is desired (and which "String" column that particular format is located in) and is therefore, unrelated to the order in which the strings occur in the Original Text. For example, the 4th or last string change in the 3rd sample (ie 10 & 1) brings about the first formatting change (ie making a superscript of the "a" in "day") in that text.

The file is attached and I hope all of this makes sense.

Thanks,

Steve

Intracellular-formatting-2017-06-30.xlsm
doable give me 1 hour or so
gowflow
ok lets agree on something the file you posted is neat and nice but not workable for a macro production. Am I allowed to change the extra blank lines that you have there just for clarity or you want the data exactly as laid out ? I am talking about the extra blanck colums and extra blank rows on top of titel and ender format etc... not the real content of cells

Can I have your ok to modify  ?
gowflow
I am looking at the details of your file and noticed 1 thing
On the sentence The big Day ...
I noticed that all your format starts with F capital then the green format is all capital do you intend to also change the capitalization ?? as it is not respected sometime you use Big A like the green and the rest are small a how did you determine this ??

I think you did not think of this issue.

I stoped developing waiting for your answer.

In my mind you have less flexibility in your option but then its your call.
gowflow
Comment by: gowflow
the file you posted is...not workable for a macro production
If the file as I posted it is definitely "not workable for a macro" (which I interpret as: its not possible for the macro to function with only the information currently included), then it would help me to understand why it's not workable or, more importantly, what else is needed so that it will be workable. Furthermore, if it absolutely cannot be done this way then yes, by all means, go ahead and add whatever is needed so that it is workable.

Comment by: gowflow
Am I allowed to change the extra blank lines that you have there just for clarity or you want the data exactly as laid out ?
I'm a bit unclear on what you mean by "just for clarity". If "[changing] the extra black lines", etc, creates greater clarity for the code (ie gives the code information that it needs in order to operate correctly and which it will otherwise not have), then yes, make any changes necessary for it to function properly.

On the other hand, if the "clarity" you are describing refers to giving me (the user) additional information, then I would say no, let's leave it as it is since, in its current form, it is clear and gives me what I need.

I prefer the data "exactly as laid out" unless, as described in the previous paragraph, it's impossible to have the macro work without making changes.
Comment by: gowflow
all your format starts with F capital then the green format is all capital do you intend to also change the capitalization ??
Good pickup and you are correct, I did not think about this issue.

I don't really care about capitalization and was not really paying that much attention to it when I created the current file. In other words, I chose all the formats arbitrarily. However, it appears that the Stencil font (which I used for String 3) is a capitalization font (ie it capitalizes every character regardless). So, to create the next screenshot, I :

User generated image
1st, first entered some lowercase characters in B2
2nd, formatted cell B4 to Stencil
3rd, inserted the formula =B2 in cell B4

...and as is shown, the characters changed to uppercase simply due to the font.
ok lets cut the disscussions and see to reality.

The macro I modified give you exactly your output desired. I kept in sheet1 original your original layout and now in Sheet1 you have the buttons and the macro attached to it. Simply run it and check the results.

For easy reference this is the code. (BTW if your absolutely after the MID finction you will be disappointed greatly as I do not use it at all in my code) it has no effect whatsoever as it is used to exctract part of data whereas in your case we are applying format to art of data MID has no effect on format. We use the Caracters property to concentrate our focusing on the caraters we need.

Here is the code

Option Explicit

Sub FormatInside()
Dim WS As Worksheet
Dim MaxRow As Long, MaxCol As Long
Dim I As Long, J As Long
Dim sText As String, sReplace As String
Dim istart As Integer, itot As Integer


'---> Disable Events
With Application
    .EnableEvents = False
    .DisplayAlerts = False
'    .ScreenUpdating = False
End With

'---> Set Variables
Set WS = ActiveSheet
MaxRow = WS.UsedRange.SpecialCells(xlCellTypeLastCell).Row
MaxCol = 11
WS.Range("L3:L" & MaxRow).ClearContents
WS.Range("L3:L" & MaxRow).ClearFormats

'---> Start Process
For I = 3 To MaxRow
    
    If WS.Cells(I, "A") <> "" Then
        '---> Copy Original entire sentence to Formated as is to get font/color same as original.
        WS.Cells(I, "A").Copy WS.Cells(I, "L")
        
        '---> Loop thru the formating
        For J = 2 To MaxCol Step 2
            If WS.Cells(2, J) <> "" Then
            
                '---> Spot Start and total length
                istart = WS.Cells(I, J)
                itot = WS.Cells(I, J + 1)
                If istart <> 0 And itot <> 0 Then
                    
                    WS.Range("L" & I).Characters(istart, itot).Font.Background = WS.Cells(2, J).Font.Background
                    WS.Range("L" & I).Characters(istart, itot).Font.Bold = WS.Cells(2, J).Font.Bold
                    WS.Range("L" & I).Characters(istart, itot).Font.Color = WS.Cells(2, J).Font.Color
                    WS.Range("L" & I).Characters(istart, itot).Font.FontStyle = WS.Cells(2, J).Font.FontStyle
                    WS.Range("L" & I).Characters(istart, itot).Font.Italic = WS.Cells(2, J).Font.Italic
                    WS.Range("L" & I).Characters(istart, itot).Font.Name = WS.Cells(2, J).Font.Name
                    WS.Range("L" & I).Characters(istart, itot).Font.Size = WS.Cells(2, J).Font.Size
                    WS.Range("L" & I).Characters(istart, itot).Font.Strikethrough = WS.Cells(2, J).Font.Strikethrough
                    WS.Range("L" & I).Characters(istart, itot).Font.Subscript = WS.Cells(2, J).Font.Subscript
                    WS.Range("L" & I).Characters(istart, itot).Font.Superscript = WS.Cells(2, J).Font.Superscript
                    
                    WS.Range("L" & I).Characters(istart, itot).Font.ThemeFont = WS.Cells(2, J).Font.ThemeFont
                    WS.Range("L" & I).Characters(istart, itot).Font.TintAndShade = WS.Cells(2, J).Font.TintAndShade
                    WS.Range("L" & I).Characters(istart, itot).Font.Underline = WS.Cells(2, J).Font.Underline
                End If
            End If
        
        Next J
    End If
Next I

WS.Range("L:L").EntireColumn.AutoFit

'---> Enable Events
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

'---> Advise User
MsgBox "Text formated created in Col L", vbInformation, "Format Cells"

End Sub

Open in new window



Anyway, look at the results and let me know.

gowflow
Intracellular-formatting-V01.xlsm
gowflow, your previous codes have all been great and I have learned something from each of them but this one is a homerun! Thank you so much for writing & posting it.

In a previous comment, I said:

just a single question which I believe, once addressed, will result in a usable and possibly finished code.

Well, I'm happy to say that this is very much a "usable code" and although I still have questions, it is already at a point where it will be a huge help.

I have been doing some testing and shown below is a list in which:

    [Yes] = Capability is present and seems to work great
    [No?] = Capability seems to be lacking but hopefully can be included
    [Rq?] = Request for modification

Also, the following abbreviations may be included:
    original = Original Text
    result(s) = Formatted Text
    clear = click Clear button
    c/p = Copy & Paste

I was hesitant to post this list because I have a screenshot to accompany each item with an asterisk. Therefore, I'm concerned that some of the brief written items may not make complete sense. However, I wanted to get a reply posted before heading off to bed. Hopefully doing so will at least allow me to convey how great this is and how much I appreciate it. I could seriously just put it to work as is and it would be super. But hopefully there are some items I've included which are quite easily doable and will make it that much better.

So here is the full list and I will try to get images and additional comments posted ASAP


    [Yes]  _01. Clicking green "Format" button correctly produces results.
    [Yes]  _02. Clicking yellow "Clear" button correctly clears results.
    [Yes]  _03. Can clear results manually with no adverse effects.
    [Yes]  _04. Clearing is not required before clicking the "Format" button again.
    [Yes]  *05. Can change the order of rows without adverse effect.
    [Yes]  *06. Additional rows function in the same way as the first three.
    [Yes]  *07. Can insert blank rows between formatting rows without interrupting function.
    [Yes]  *08. Can unmerge all cells without adverse effect
    [Yes]  *09. Can c/p result to another cell with new formatting preserved.
    [Yes]  *10. Can c/p result to another app with new formatting preserved.
    [Yes]  *11. Can change Format in a String column and results in all rows update accordingly.
    [Yes]  *12. Changing numerical values accurately changes the reformatted string.
    [Yes]  *13. Can have formulas in numerical values cells without adverse effect.
    [Yes]  *14. Can add preview formulas without affecting the results.
    [Yes]  *15. Can special-paste Formats to preview cells without changing results.

    [No?]  *16. Cannot create additional String columns.
    [No?]  *17. Cannot include a formula in Original Text column.
    [No?]  *18. Cannot have non-functioning rows which have content in Original Text column
    [No?]  *19. Cannot insert leading column(s).
    [No?]  *20. Cannot insert spacing column(s).
    [No?]  *21. Cannot insert leading rows(s).

    [Rq?]  _22. Please place "Format" button near the right side of column A.
    [Rq?]  *23. Please remove final pop-up:
    [Rq?]  _24. If possible, please modify so auto-calculate duplicates action of the Format button.
    [Rq?]  _25. Is there a way to auto-format previews?

Thanks again,

Steve
ok fine glad you liked it and Impressed about your thorough testing which is really impressive and not seen. I am usually picky, but you blew me big time !!!

When it comes to some of the NO you understand they are obviously NO as
1) They are not part of the request
2) Every Macro need to have a framework of reference (you cannot expect data in column A to suddenly have this data in Col B or C and expect the macro to zippy deee daaa and find it !!
3) I am not saying I cannot cater for unforeseen and have more flexibility but for sure this will be a different scope and not this question.

For the Rq? and [No] here are my answers
[No?]  *17. Cannot include a formula in Original Text column. [Done]
[No?]  *18. Cannot have non-functioning rows which have content in Original Text column [Done]
[No?]  *21. Cannot insert leading rows(s). [Done] (However, row 1 and row2 need to stay as is !!! Data in row 3 as from row 3 put as many blank row as you want.)
[Rq?]  _22. Please place "Format" button near the right side of column A. [Done]
[Rq?]  *23. Please remove final pop-up: [Done]

[Rq?]  _24. If possible, please modify so auto-calculate duplicates action of the Format button.
[Rq?]  _25. Is there a way to auto-format previews?
These 2 items are not clear for me please clarify what you need.

Here is the updated file. the 3rd item has a formula.
Try all and let me know.
gowflow
Intracellular-formatting-V02.xlsm
gowplow, you are awesome!

This is Stunning! — and about the coolest thing I think I've seen in all my 12+ years since joining EE!

I went through my list of tests again and I feel like sort of a heel mentioning anything your code can't do because I'm so excited about all the great things it can do — but you already know about those. LOL

So here are some notes re modifications I hope are still possible:

16. Cannot create additional String columns.
This refers to the possibility of adding additional String columns. Right now (in your latest version), there are five String columns meaning that a maximum of 5 different Formats can be applied. It's probably unlikely that a single row of Original Text will need more than five types of formatting applied, but I think it is quite likely that more than five types of formatting will be used over many rows of Original Text entries.

As shown in the following screenshot, I attempted to create an additional formatting option (String 6) by copying String 5 (columns J & K), then inserting them ahead of the Formatted Text column (ie the intent was to have the new String 6 occupy columns L & M which moves Formatted Text to column N):

User generated image
But as shown in the next screenshot, it appears that Formatted Text has sort of an "absolute" connection to column L because after inserting String 6, clicking the "Format" button still places the results in column L and clicking "Clear" removes all content (even numerical values for String 6) residing in column L:

User generated image
Is it possible therefore, to change the column where the results (Formatted Text) appear to a "relative" column which is determined by and resides at the end of the number of String columns present?

For example, columns in the most recent file (Intracellular-formatting-V02.xlsm) are arranged as follows:

A — Original Text
B & C — String 1
D & E — String 2
F & G — String 3
H & I — String 4
J & K — String 5
L — Formatted Text

But, suppose you wanted to have say, 19 formatting options (as in your first draft). In that case, it would be:

A — Original Text
B & C — String 1
D & E — String 2
F & G — String 3
H & I — String 4
J & K — String 5
L & M — String 6
N & O — String 7
P & Q — String 8
R & S — String 9
T & U — String 10
V & W — String 11
X & Y — String 12
Z & AA — String 13
AB & AC — String 14
AD & AE — String 15
AF & AG — String 16
AH & AI — String 17
AJ & AK — String 18
AL & AM — String 19
AN — Formatted Text

which would place the results (Formatted Text) in column AN.

The point is that, regardless of the number of Format options, the results (Formatted Text) column would always follow at the end (just after the last String).

And if doing the above is not possible (ie if the position of the results cannot be relative), could the code at least specify or start with many more than five formatting options? Having more formatting options than you might need seems better than not having enough since with many present, you can always use Data > Outline > Group and then easily hide the inactive/unneeded Formats.

17. Cannot include a formula in Original Text column.
This item is perfect now. I mention it here only because it was a BIGGIE on my list of hopeful changes and I'm very happy that you were able to modify it. The reason is that the majority of my Original Text column entries will be based on formulas rather than actual PasteValue-type text.

18. Cannot have non-functioning rows which have content in Original Text column.
I did not clarify the meaning of this item very well but it's probably just as well and I will defer doing so now because, at this point, I don't think it's too important; plus, I think it would throw a monkeywrench into the works.

19. Cannot insert leading column(s).
This item, and #20, are closely related to #16 because all three involve columns.

#19 has to do simply with inserting additional blank columns to the left of Original Text as shown in the next two screenshots:

Single leading column (column A) inserted:User generated image
But having the leading column creates problems with the results:User generated image
I would really like to be able to freely insert (and/or delete) at the very beginning (ie left of the Original Text column), not just one column, but as many columns as I might need while working in the spreadsheet. In other words, it would be super if, as an extreme example, I could insert say, 208 columns at the beginning or far left of the spreadsheet (which would move Formatted Text to column HA) without affecting the workings or results of the code. Is that doable (I hope)?

20. Cannot insert spacing column(s).
Following are a couple of screenshots for this item but it is for cosmetic purposes only and is not really too important. Therefore, if implementing it is problematic, please feel free to disregard.

Spacing columns (B & M) inserted:User generated image
But they create problems with the results:User generated image
21. Cannot insert leading rows(s).
Comment by gowflow
...row 1 and row2 need to stay as is!!! Data in row 3 as from row 3 put as many blank row as you want.
I understand and that's perfectly fine.


I still need to explain #24 & #25 but I will stop here for tonight.

By the way, I wish this thread had 5000 points to award instead of only 500. :P

Many thanks,

Steve
Tks your appreciation and glad it is meeting your requirement.

Maybe I did not express myself correctly. I will say it again.

I HAVE A SOLUTION FOR ALL YOUR NO's

What I could do in this question has been done. the rest should be the scope of a new question or else we will be loosing the planet on our way already the threads are long.

I am only missing clarification of item 24 and 25 (which deep down inside have an idea about but rather see the real explanation). these 2 should be addressed in the new question.

Hope I have clarified.
gowflow
Yes, I should have done that. I will open a new thread now so no need to answer anything here.
Do you still need some help on this question ?
gowflow