Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy a number into a text cell, keeping leading zeros? | Charts and Charting in Excel | |||
Lookup certain text within a cell, PLEASE HELP! | Excel Worksheet Functions | |||
lookup a text cell and return text | Excel Discussion (Misc queries) | |||
copy a formatted cell to another sheet as text without format | Excel Discussion (Misc queries) | |||
Copy text from same cell on every sheet to title sheet? | Excel Discussion (Misc queries) |