Remove characters and numbers from cell

[Jr66]

If I had this in cell N3 , then n4 etc down to n50 what is easiest way to remove numbers and characters and put in cell beside. sometimes characters are different, some times there are 6 numbers, or 7 numbers etc

Cell N3 Steve Smith 555-888-1111
Cell N4 Joe Kerr 1-444-975-2222

Thanks

Jeff

[avk]

Suppose in "N3" is : Steve Smith 555-888-1111
for remove name & show number only in "O3"

Please Login or Register  to view this content.

for remove number

Please Login or Register  to view this content.

[63falcondude]

Assuming the name will always be of the form "First Last" or "Last First" (i.e. always one space), you can use

O3 =LEFT(N3,FIND(" ",N3,FIND(" ",N3)+1)-1) for the name and

P3 =SUBSTITUTE(N3,O3,"") for the phone number.

[AlKey]

For the Name:
Enter in O3 and copy down

Please Login or Register  to view this content.

then enter array formula in P3 and copy down. Format as General.

Please Login or Register  to view this content.

**Array formula must be entered with Ctrl+Shift+Enter key combination.

v	N	O	P
3	Steve Smith 555-888-1111	Steve Smith	5558881111
4	Joe Kerr 1-444-975-2222	Joe Kerr	14449752222