Seperating Numbers from Letters in Cells..

  • Thread starter Thread starter JohnTLand
  • Start date Start date
J

JohnTLand

Hi,

I have about 1500 cells that I need to seperate the NUMBERS from th
LETTERS. I would Ultimatly like the two to be in seperate fields.

The data looks like this NOW:

44 1189885906Marc Van de Velde
2083211260Jim Allen
9788587719Mike Hodgson
15614316181David R Wong
4089568787Eric Singh
2129670672Kamal Ahmed
9038856860Mac Herman
6612681321Molly Ball
9099319674Stefan Riderer
9524321222Scott Frondal
7142838669Melissa Rodriguez
7036068975Duke Park
9784220212Bob Burgess


The data Needs to look like:

44 1189885906 | Marc Van de Velde
2083211260 | Jim Allen
9788587719 | Mike Hodgson
15614316181 | David R Wong
4089568787 | Eric Singh
2129670672 | Kamal Ahmed
90388568603324 | Mac Herman
6612681321 | Molly Ball
909931967456 | Stefan Riderer
9524321222 | Scott Frondal
7142838669 | Melissa Rodriguez
703606897545 | Duke Park
9784220212 | Bob Burgess


I would think there is a formula for this action, but have yet to fin
any information on it.


Thanks,

JT
 
Hi
try the following:
in B1 enter the following array entered formula (entered with
CTRL+SHIFT+ENTER)
=LEFT(A1,MAX(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:1024")),1)),ROW(INDIR
ECT("1:1024")),0)))

in C1 enter
=MID(A1,MAX(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:1024")),1)),ROW(INDIRE
CT("1:1024")),0))+1,1024)

copy both formulas down. after this you may want to copy the date and
insert it again as values (use 'Edit - Paste Special - Values' for
this).
 
Back
Top