Paul <[EMAIL PROTECTED]> skrev den Thu, 28 Apr 2005 11:46:41 +1200:
There is no function out of the box for this. It can be achieved using a publicy released macro found here: http://www.ooomacros.org/user.php
/paul
On 4/28/05, Bob Cataldo <[EMAIL PROTECTED]> wrote:I am trying to figure out how to convert text to columns in an Open Office Calc (spread sheet).
for example: I want to take a cell which has name ( first and last ) and split it into 2 ( or more ) columns of First Name and Last name - use the space as a separator.
or take an e-mail address and use the @ as a separator.
Any suggestions would be appreciated.
Bob Cataldo
Here is my suggestion, but first I will try to translate the function names, since I use the Swedish version of OOo:
VÄNSTER=LEFT
HÖGER=RIGHT
HITTA=FIND, but I am not sure about if this is the word to use in this case. If not, try FOUND, SEARCH or something like that. The correct function name can be found by pressing Ctrl+F2, then select category text, then look for it there.
LÄNGD=LENGTH. If that doesn't work, try LEN, TEXTLEN, TEXTLENGTH, STRLEN or something like that. It's been a while since I used the English version, so I am not 100% sure, as you already noticed...
All these four functions can be found at Ctrl-F2 -> Functions -> Category: Text.
So, here's may example:
Input your name into A1, then your first name will appear in B1 and your last name in C1. A1="Firstname Secondname" Strange name, but again, this is an example...
B1=VÄNSTER(A1;HITTA(" ";A1)-1)
In this case "Firstname" is returned.
English version (and now I guess): B1=LEFT(A1;FIND(" ";A1)-1)C1=HÖGER(A1;LÄNGD(A1)-HITTA(" ";A1))
In this case "Secondname" is returned.
English version (guessing again): C1=RIGHT(A1;LENGTH(A1)-FIND(" ";A1))If A1="" (empty), then B1 and C1 will contain #VALUE, which doesn't look any good. To avoid it, try this:
B1=IF(A1="";"";LEFT(A1;FIND(" ";A1)-1))
C1=IF(A1="";"";RIGHT(A1;LENGTH(A1)-FIND(" ";A1)))If no " " is found, there will also be an error, so if only one name is entered you could do something like this:
B1=OM(A1="";"";OM(ÄRTAL(HITTA(" ";A1));VÄNSTER(A1;HITTA(" ";A1)-1);A1))
C1=OM(A1="";"";OM(ÄRTAL(HITTA(" ";A1));HÖGER(A1;LÄNGD(A1)-HITTA(" ";A1));""))
Now I have to guess English function names again: ÄRTAL could be something like ISVALUE. OM=IF.
So:
B1=IF(A1="";"";IF(ISVALUE(FIND(" ";A1));LEFT(A1;FIND(" ";A1)-1);A1))
C1=IF(A1="";"";IF(ISVALUE(FIND(" ";A1));RIGHT(A1;LENGTH(A1)-FIND(" ";A1));""))
Now, if no space is found, B1=A1 and C1="", assuming that when the user enter only one name, it is the first name.
I tried to make this shorter:
B1=IF(ISVALUE(FIND(" ";A1));LEFT(A1;FIND(" ";A1)-1);A1)
C1=IF(ISVALUE(FIND(" ";A1));RIGHT(A1;LENGTH(A1)-FIND(" ";A1));"")However, when entering nothing into A1, B1=0 and C1="". Doesn't look very good with a zero in B1, does it?.
B1=IF(ISVALUE(FIND(" ";A1));LEFT(A1;FIND(" ";A1)-1);"") would solve this, but then B1="" if only one name is entered to A1.
For email addresses (English guessed version):
A1="[EMAIL PROTECTED]"
B1=IF(A1="";"";IF(ISVALUE(FIND("@";A1));LEFT(A1;FIND("@";A1)-1);A1))
"name"C1=IF(ISVALUE(FIND("@";A1));RIGHT(A1;LENGTH(A1)-FIND("@";A1));"")
"email.address.com"I always use Ctrl+F2 when I am not sure about the exact function names, and then I build the cell formula from there. There you can also see the structure of the formula and you can see the resulting value of it during input (if valid) and you can view all available functions with a short explanation for each one of them. Try it, if you didn't already...
Johnny
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
