I just wrote a function for this:
Function SeparateWords(Sentence As String, GetWordNo As Integer, Separator
As String) As String
' Sentence is the input string from which we want one word only.
' GetWordNo is a number needed to decide which word to get from the sentence.
' GetWordNo=1 means that we want the first word in the sentence.
' Separator is a string separating each word from each other. In ordinary
' sentences, " " (space) would be a good idea of a separator.
Dim i As Integer ' Used in the Do-Loop later on.
Dim CurrentPosition As Integer
Dim NextPosition As Integer
' CurrentPosition and NextPosition are used to seach for the separator string surrounding
' the word we are looking for.
If GetWordNo<1 Then ' GetWordNo has to be 1 or more, otherwise return an empty string.
SeparateWords=""
Exit Function
EndIf
' This loop is supposed to find the position of the separator located right after
' the word we are looking for.
Do
CurrentPosition=NextPosition
NextPosition=InStr(CurrentPosition+1, Sentence, Separator)
i=i+1
Loop While i<GetWordNo And NextPosition>0 ' NextPosition=0 means that no separator was found.
' After the last word there is probably no separator, so in that case NextPosition is likely
' to be 0. So, if this is the case, just let NextPosition be the length of the sentence + 1,
' just like pretending that there is a separator right after the last word, even when there's
' not.
If NextPosition=0 Then
NextPosition=Len(Sentence)+1
Endif
' Maybe the loop above was finished before i reached the value of GetWordNo. This means that
' there are not as many words in the sentence as we thought, hoped or expected. If this is
' the case, just return an empty string. Otherwise return the word we are looking for.
If i=GetWordNo Then
SeparateWords=Mid(Sentence, CurrentPosition+1, NextPosition-CurrentPosition-1)
Else
SeparateWords=""
EndIf
End Function
Copy this to the clipboard (Ctrl+C), then click Tools -> Macros -> Macro...
Select soffice/Standard
Click Edit
Position the cursor at the end of the text if there is any.
Paste clipboard (Ctrl-V).
Maybe there are some line breaks in the code that needs to be eliminated, so you might need to do some editing.
You may remove my comments if you like, or replace them with your own words of course.
Now, close the Macro Window to go back to your spreadsheet. Enter in A1: Johnny Åke Andersson (That's my full name, maybe its more fun to enter your own instead...)
Enter in B1: =SEPARATEWORDS(A1;1;" ") This gets the first word in the sentence in A1, word separator = space.
Enter in C1: =SEPARATEWORDS(A1;3;" ") This gets the third word in the sentence in A1, word separator = space.
Enter in D1: =SEPARATEWORDS(A1;2;" ")
Now the following appears in the following cells:
A1: Johnny Åke Andersson B1: Johnny C1: Andersson D1: Åke
Also try this:
[EMAIL PROTECTED] B2=SEPARATEWORDS(A2;1;"@") C2=SEPARATEWORDS(A2;2;"@")
Now B2 shows johnny.guitar, C2 shows telia.com We have separated an email address...
Maybe this is much easier than the example I sent before, at least when you pasted the function to the right place. If you want another name for the function, don't forget to change its name EVERYWHERE where it occurs. Use Find/Replace, just to be sure you don't miss something.
Best regards
Johnny Andersson
Johnny Andersson <[EMAIL PROTECTED]> skrev den Thu, 28 Apr 2005 13:08:31 +0200:
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
-- Johnny Andersson Sörgårdsvägen 4, 5 tr 445 37 BOHUS
031-98 26 74 073-99 0 77 88
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
