Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FORMULAS TO JOINT CELLS (2000 sr 1)

    In this sheet is present a list of ID.

    My problem:

    Joint in column B the ID present in column D E F
    Joint in column C the ID present in column G H

    Concatenate the ID with ";" separator

    But if in one of column D E F or G H not is present ID conactenate only th ID present

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FORMULAS TO JOINT CELLS (2000 sr 1)

    A formula like

    =SUBSTITUTE(TRIM(D2&" "&E2&" "&F2)," ",";")

    In B2 will do it. Extend as required

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FORMULAS TO JOINT CELLS (2000 sr 1)

    Tks, but is possible to get the result in B and in C in text format?

  4. #4
    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: FORMULAS TO JOINT CELLS (2000 sr 1)

    I don't understand, B& C are text since they have numbers and text already.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FORMULAS TO JOINT CELLS (2000 sr 1)

    That formula returns a text result in the form you asked for.

    It takes the values in D E and F and separates them with spaces.

    The TRIM function removes any extra spaces caused by missing values in D, E or F

    The Substitute function turns the remaining spaces into ; characters

    Result is a string that may look like "O12345;O5678"

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FORMULAS TO JOINT CELLS (2000 sr 1)

    hum....
    i am confused, please attache my file with your modify.
    Tks.

  7. #7
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FORMULAS TO JOINT CELLS (2000 sr 1)

    Look in cells B2 or C2

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FORMULAS TO JOINT CELLS (2000 sr 1)

    OK! But if i click in B and in C i see a formulas, i want the result string in txt format...

  9. #9
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FORMULAS TO JOINT CELLS (2000 sr 1)

    The formula is a formula, the "value" is a text field (the one that displays).

    However, if you want to remove the formulae after they have set the values
    You could select the whole of columns B & C
    Copy them to the Clipboard, then "Paste Special" selecting the "values" radio button and press Enter afterwards

    This would overwrite the formula with the value of the result

Posting Permissions

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