Isolating words and numbers to a different column
 

Hi I have copied an adress list into a wpreadsheet, where both names,
mailadresses and phonenumbers are at one line. Is there a formula where I can
isolate e.c. mailadresses to the next column automatically? I know left and
right formulars, but is there something like that, but more like IF(the word
conatins @) or something like that? And that goes for the numbers as well, so
that all numbers goes to column C?
Thanks

--
Therese

Isolating words and numbers to a different column
 

On Tue, 19 Sep 2006 06:24:01 -0700, Therese
wrote:

Hi I have copied an adress list into a wpreadsheet, where both names,
mailadresses and phonenumbers are at one line. Is there a formula where I can
isolate e.c. mailadresses to the next column automatically? I know left and
right formulars, but is there something like that, but more like IF(the word
conatins @) or something like that? And that goes for the numbers as well, so
that all numbers goes to column C?
Thanks

Yes it can be done.

You will need to post samples of your data, and your expected output.


--ron

Isolating words and numbers to a different column
 

uh...what do you mean?
to you? Or can I explain better. In A1 I have Jane Dine
38866441. I want Jane Dine in A1,
in B1, and 38866441 in C1. Can that be done automatically in a formular I can
copy down through the list?
Thanks man ;-)
--
Therese


"Ron Rosenfeld" wrote:

On Tue, 19 Sep 2006 06:24:01 -0700, Therese
wrote:

Hi I have copied an adress list into a wpreadsheet, where both names,
mailadresses and phonenumbers are at one line. Is there a formula where I can
isolate e.c. mailadresses to the next column automatically? I know left and
right formulars, but is there something like that, but more like IF(the word
conatins @) or something like that? And that goes for the numbers as well, so
that all numbers goes to column C?
Thanks

Yes it can be done.

You will need to post samples of your data, and your expected output.


--ron

Isolating words and numbers to a different column
 

On Wed, 20 Sep 2006 08:05:03 -0700, Therese
wrote:

uh...what do you mean?
to you? Or can I explain better. In A1 I have Jane Dine
38866441. I want Jane Dine in A1,
in B1, and 38866441 in C1. Can that be done automatically in a formular I can
copy down through the list?
Thanks man ;-)

It can't be done with a formula because you cannot, in Excel write a formula
that will change the contents of A1.

If you can accept having the parsed results in B1, C1 and D1, then it can be
done with formulas. Otherwise you will need to execute a VBA Macro.

For formulas, one way is to download and install Longre's free morefunc.xll
add-in from http://xcell05.free.fr

Then use these formulas:

All the formulas assume that
the last "word" is the long number
the next to last "word" is the email address with no <spaces
the remainder is the name


B1: =REGEX.MID(A1,".*(?=\s\S+@\S+\s)")
C1: =REGEX.MID(A1,"\b\S+@\S+\b")
D1: =REGEX.MID(A1,"\w+",-1)

You could also use these built-in functions, but they are more cumbersome:

B1:
=LEFT(A1,-1+FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))

C1:
=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))
+1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(CHAR(1),
SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))-1))-1)

D1:
=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255)


--ron

Isolating words and numbers to a different column
 

wooow. Thanks. I will need some time to get it work but thanks a lot for your
job.

Therese


"Ron Rosenfeld" wrote:

On Wed, 20 Sep 2006 08:05:03 -0700, Therese
wrote:

uh...what do you mean?
to you? Or can I explain better. In A1 I have Jane Dine
38866441. I want Jane Dine in A1,
in B1, and 38866441 in C1. Can that be done automatically in a formular I can
copy down through the list?
Thanks man ;-)

It can't be done with a formula because you cannot, in Excel write a formula
that will change the contents of A1.

If you can accept having the parsed results in B1, C1 and D1, then it can be
done with formulas. Otherwise you will need to execute a VBA Macro.

For formulas, one way is to download and install Longre's free morefunc.xll
add-in from http://xcell05.free.fr

Then use these formulas:

All the formulas assume that
the last "word" is the long number
the next to last "word" is the email address with no <spaces
the remainder is the name


B1: =REGEX.MID(A1,".*(?=\s\S+@\S+\s)")
C1: =REGEX.MID(A1,"\b\S+@\S+\b")
D1: =REGEX.MID(A1,"\w+",-1)

You could also use these built-in functions, but they are more cumbersome:

B1:
=LEFT(A1,-1+FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))

C1:
=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))
+1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(CHAR(1),
SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))-1))-1)

D1:
=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255)


--ron

Isolating words and numbers to a different column
 

On Thu, 21 Sep 2006 08:31:01 -0700, Therese
wrote:

wooow. Thanks. I will need some time to get it work but thanks a lot for your
job.

Therese



You're welcome. Let me know how it works for you.


--ron

Isolating words and numbers to a different column
 

Hi Therese

As an alternative to the formula method, you could use the DataText to
columns wizard.
Work on a copy of your data - Just in case!!!!

With all of your data in column A
Mark column A
DataText to ColumnsDelimitedNextClick SpaceNextFinish
That gives you a column of
Forenames in A
Last in B
email address in C
Number in D

Insert a new column at C
in C1 =A1&" "&B1 and copy down
Mark the whole of column C, CopyPaste SpecialValues to "fix" the data.
Delete columns A and B


--
Regards

Roger Govier


"Therese" wrote in message
...
wooow. Thanks. I will need some time to get it work but thanks a lot
for your
job.

Therese


"Ron Rosenfeld" wrote:

On Wed, 20 Sep 2006 08:05:03 -0700, Therese

wrote:

uh...what do you mean?
to you? Or can I explain better. In A1 I have Jane Dine
38866441. I want Jane Dine in A1,

in B1, and 38866441 in C1. Can that be done automatically in a
formular I can
copy down through the list?
Thanks man ;-)

It can't be done with a formula because you cannot, in Excel write a
formula
that will change the contents of A1.

If you can accept having the parsed results in B1, C1 and D1, then it
can be
done with formulas. Otherwise you will need to execute a VBA Macro.

For formulas, one way is to download and install Longre's free
morefunc.xll
add-in from http://xcell05.free.fr

Then use these formulas:

All the formulas assume that
the last "word" is the long number
the next to last "word" is the email address with no <spaces
the remainder is the name


B1: =REGEX.MID(A1,".*(?=\s\S+@\S+\s)")
C1: =REGEX.MID(A1,"\b\S+@\S+\b")
D1: =REGEX.MID(A1,"\w+",-1)

You could also use these built-in functions, but they are more
cumbersome:

B1:
=LEFT(A1,-1+FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))

C1:
=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))
+1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(CHAR(1),
SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))-1))-1)

D1:
=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255)


--ron

Isolating words and numbers to a different column
 

Hi
The tip was fab!!!
Merci!!
:-)
--
Therese


"Roger Govier" wrote:

Hi Therese

As an alternative to the formula method, you could use the DataText to
columns wizard.
Work on a copy of your data - Just in case!!!!

With all of your data in column A
Mark column A
DataText to ColumnsDelimitedNextClick SpaceNextFinish
That gives you a column of
Forenames in A
Last in B
email address in C
Number in D

Insert a new column at C
in C1 =A1&" "&B1 and copy down
Mark the whole of column C, CopyPaste SpecialValues to "fix" the data.
Delete columns A and B


--
Regards

Roger Govier


"Therese" wrote in message
...
wooow. Thanks. I will need some time to get it work but thanks a lot
for your
job.

Therese


"Ron Rosenfeld" wrote:

On Wed, 20 Sep 2006 08:05:03 -0700, Therese

wrote:

uh...what do you mean?
to you? Or can I explain better. In A1 I have Jane Dine
38866441. I want Jane Dine in A1,

in B1, and 38866441 in C1. Can that be done automatically in a
formular I can
copy down through the list?
Thanks man ;-)

It can't be done with a formula because you cannot, in Excel write a
formula
that will change the contents of A1.

If you can accept having the parsed results in B1, C1 and D1, then it
can be
done with formulas. Otherwise you will need to execute a VBA Macro.

For formulas, one way is to download and install Longre's free
morefunc.xll
add-in from http://xcell05.free.fr

Then use these formulas:

All the formulas assume that
the last "word" is the long number
the next to last "word" is the email address with no <spaces
the remainder is the name


B1: =REGEX.MID(A1,".*(?=\s\S+@\S+\s)")
C1: =REGEX.MID(A1,"\b\S+@\S+\b")
D1: =REGEX.MID(A1,"\w+",-1)

You could also use these built-in functions, but they are more
cumbersome:

B1:
=LEFT(A1,-1+FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))

C1:
=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))
+1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(CHAR(1),
SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))-1))-1)

D1:
=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255)


--ron