# Thread: FORMULAS TO JOINT CELLS (2000 sr 1)

1. ## 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. ## 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. ## 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. ## 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. ## 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"

hum....
Tks.

7. ## Re: FORMULAS TO JOINT CELLS (2000 sr 1)

Look in cells B2 or C2

8. ## 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. ## 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
•