Isolating words and numbers to a different column

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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

 

[Ron Rosenfeld]

uh...what do you mean?
to you? Or can I explain better. In A1 I have Jane Dine
(e-mail address removed) 38866441. I want Jane Dine in A1, (e-mail address removed)
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

 

[Guest]

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

Therese

 

[Ron Rosenfeld]

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

 

[Roger Govier]

Hi Therese

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

With all of your data in column A
Mark column A
Data>Text to Columns>Delimited>Next>Click Space>Next>Finish
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, Copy>Paste Special>Values to "fix" the data.
Delete columns A and B

 

[Guest]

Hi
The tip was fab!!!
Merci!!

--
Therese

Hi Therese

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

With all of your data in column A
Mark column A
Data>Text to Columns>Delimited>Next>Click Space>Next>Finish
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, Copy>Paste Special>Values to "fix" the data.
Delete columns A and B