Parse Data

  • Thread starter Thread starter ralph ferrara
  • Start date Start date
R

ralph ferrara

I have a large list of data and I need to separate the numbers into a
separate column. My data looks like this:

Abasraju(C. Blanca, Peru) 297-8
Achey, Jeff 67
Adi Kailash "Little Kailash" (India) 365-6
Africa 338-9, 342, 346-8
Agua Negra 1980: 99-108

I want to put the text in column A, and move the numerical values to
column B. I would appreciate any help. Thanks Ralph
 
Assume the data starts in E1 going down, in A1 put this formula

=TRIM(LEFT(E1,MATCH(TRUE,ISNUMBER(--MID(E1,ROW(INDIRECT("1:"&LEN(E1)))+1,1))
,0)))

enter with ctrl + shift & enter

copy down as long as needed, in B1 to get the pages

=TRIM(SUBSTITUTE(E1,A1,""))

copy down as long as needed. Select both columns, copy,
then edit>paste special as values in place..
 
Back
Top