Lookup text then copy over to new cell
 

I have a spreadsheet setup and in one cell there are address details but in
between the address details there is a square symbol.

In some of the cells there are up to 6 different address lines with some
only have 1 or 2 address lines in there.

What I would like it to do is take the text from Initially before the first
symbol and bring that text into an address line 1 cell and then from after
that take the text from just after the symbol to the next symbol and bring
that text through into the next possible cell.

Hope this makes sense.

Lookup text then copy over to new cell
 

I believe the square symbol you refer to is probably the line feed character
(char(10) or Alt-Enter when doing it manually) when the cell has not been
formatted to wrap text. Try these formulas to get what is left of the line
feed character and to the right:
=LEFT(A1,FIND(CHAR(10),A1)-1)
=RIGHT(A1,LEN(A1)-FIND(CHAR(10),A1))
--
Kevin Vaughn


"Jaco Jacobs" wrote:

I have a spreadsheet setup and in one cell there are address details but in
between the address details there is a square symbol.

In some of the cells there are up to 6 different address lines with some
only have 1 or 2 address lines in there.

What I would like it to do is take the text from Initially before the first
symbol and bring that text into an address line 1 cell and then from after
that take the text from just after the symbol to the next symbol and bring
that text through into the next possible cell.

Hope this makes sense.

Lookup text then copy over to new cell
 

I missed the part about possibly 6 lines worth of information. Someone may
come up with a formula for what you want or you may need to resort to VBA
code.
--
Kevin Vaughn


"Jaco Jacobs" wrote:

I have a spreadsheet setup and in one cell there are address details but in
between the address details there is a square symbol.

In some of the cells there are up to 6 different address lines with some
only have 1 or 2 address lines in there.

What I would like it to do is take the text from Initially before the first
symbol and bring that text into an address line 1 cell and then from after
that take the text from just after the symbol to the next symbol and bring
that text through into the next possible cell.

Hope this makes sense.

Lookup text then copy over to new cell
 

One other thought, perhaps you can do a ctrl-h for find and replace. Use alt
010 (I believe you need to use numbers on keypad for this) for the find value
and a character that does not exist in your data (like perhaps the ;
(semicolon) for the replace value. Then you could do data-text to columns
and use the delimiter that you just used as the replace value.
--
Kevin Vaughn


"Kevin Vaughn" wrote:

I missed the part about possibly 6 lines worth of information. Someone may
come up with a formula for what you want or you may need to resort to VBA
code.
--
Kevin Vaughn


"Jaco Jacobs" wrote:

I have a spreadsheet setup and in one cell there are address details but in
between the address details there is a square symbol.

In some of the cells there are up to 6 different address lines with some
only have 1 or 2 address lines in there.

What I would like it to do is take the text from Initially before the first
symbol and bring that text into an address line 1 cell and then from after
that take the text from just after the symbol to the next symbol and bring
that text through into the next possible cell.

Hope this makes sense.