RE: Concatenate

Hi Tharina

This is not so easy, as you will see. There are three functions, LEFT, RIGHT and MID, which allow you to obtain a fixed number of characters from a cell. This works when you have fixed width fields to split.

Use the FIND function to find the "space" in the text, as follows:

=find(" ",Cell containing text e.g. B3)

This will tell you the position of the space, eg 5 means that there are four characters to the left of the space,

You can then use the LEFT function to "get" the text to the left, eg

=left(Cell containing text,Cell ref of FIND cell minus 1)
example +LEFT(B3,C3-1)

You can then use the LEN function to find out how many characters there are in the text cell e.g.

=len(B3)

When you know this, you can use the RIGHT function to "get" the rightmost word, eg

=right(cell containing text, cell containing LEN function minus cell containing FIND function)

example =RIGHT(B3,C3-E3)

This works great when there are only two words in the cell!

Have fun!

Alan Burbridge
Best STL