Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    custom formatting lost ('97)

    I have a text box that refers to a cell that results from the concatenation of two input cells. My problem is that the custom formatting that applies to the two input cells doesn't show up in the concatenation (nor in the text box, of course). The custom formatting is a prefix ("B-").
    How can I remedy this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: custom formatting lost ('97)

    When you concatenate cells, you just concatenate their values, not their formatting. Include the formatting in the formula, using the TEXT function, e.g.

    =TEXT(A1,"B-0")&TEXT(B1,"B-0")

    (adapt as necessary)

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: custom formatting lost ('97)

    You can use the TEXT function when you convert the number putting in the custom format, something like (change as appropriate:
    =TEXT(A1,"B-"&"0")& A2

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: custom formatting lost ('97)

    Good, this helps.
    (The only hard spot there is that details of my custom formatting can vary from row to row.)

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: custom formatting lost ('97)

    <P ID="edit" class=small>(Edited by sdckapr on 30-Mar-04 11:08. Modified code and added PS)</P>If it has a pattern it might be able to part of the formula.

    You could also create a user-function which uses the TEXT property of the cell instead of the Value property that the "normal" formulas use:

    This is an example, add it to a module:

    <pre>Option Explicit
    Function CombineText(ParamArray rng()) As String
    Dim rCell As Range
    Dim sSep As String
    Dim sText As String
    Dim i As Long
    sSep = ", "
    sText = ""
    For i = 0 To UBound(rng)
    For Each rCell In rng(i)
    sText = sText & sSep & rCell.Text
    Next
    Next
    CombineText = Mid(sText, Len(sSep) + 1)
    Set rCell = Nothing
    End Function</pre>


    Use it like:
    <pre>=CombineText(A11)</pre>


    It will combine A1&B1&C1&D1 only instead of the values it will combine how the cells are displayed. it adds a "separator" (", ") between each one automatically, change as desired.

    Steve
    PS. I changed the code to also work with selecting individual ranges using the paramarray so you could also use (if desired) something like:
    <pre>=CombineText(A1,B1,D1)</pre>


Posting Permissions

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