Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Do you know that when I retired I thought I would only be browsing the lounge, but these little issues still crop up.......

    I need to end up with a cell (times very many!) that has mixed text formatting in it. This needs to be easy to administer, and the manual selection of part of the text and setting the format different does not seem practical. I had two thoughts.

    If I set the cell font and then modify the font format on part of the text at the first input, then I can search and replace based on formatting. This doesnt work because the S&R works on the basic cell format and doesnt find the modified text.

    The second was to enter the basic and variant text in two differnt cells and then concatenate (or & them) to make a composite cell. This doesnt work because the formula cell does not retain the format of the variant text.

    Making either of these work would be a great help. Or, indeed coming up with a different solution that does work is fine....

    Thanks

    Mike C

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I think you'd need code to perform the formatting. Formulas won't ever work with mixed formatting, and the S&R behaviour can't be changed either.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have now researched this a little and recognised that I am going too have to use VBA, and I think that this is best in a custom function. I am quite happy to add a couple of enabling columns that hold the lengths of the two cell entries, and I think the function in Col_C would concatenate the values for each row in col_A and col_B, then force the font name, size and style of the first cell into the first n characters (the length) and the second cell equivalents onto the the last n characters.

    I just dont know where to begin, never having used other than built-in functions. Explicit help, or pointers to source material gratefully received.

    Mike C

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You cannot apply different font formatting to parts of a formula, even if that formula is a UDF. You would have to run code - perhaps in the change event of the worksheet.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Mike,

    Could you post an example of your worksheet so we can see what the data looks like?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •