Excel 2008 : Integer Extraction Formula

[cnsader]

I need a little help on this one:
I have a column of Alphanumeric Data that I need the integer extracted into a new column:

Here's the Data in column A:

Decubitus Sacral 707.03
Breast Mass 611.72
SBO 560.81
Cholelithiasis w/o obst 574.20
Perirectal Abscess 566
SBO 560.81
Cholelithiasis w/o obst 574.20
Hernia Umbilical 553.1
SBO 560.81
Crohn's NOS 555.9
Colon B9 Neopl 211.3
Hernia Diaphragmatic 553.3
Hernia VIH 553.21
Hernia VIH 553.21
GSP 577.0
Abdominal Pain RUQ 789.01
Abdominal Pain RUQ 789.01
Cholecystitis Chronic 575.11
Hernia VIH 553.21
Fournier's Gangrene 608.83
Abdominal Pain LLQ 789.04
LN Malig Groin 196.5
Colon Cancer Cecum 153.4
Breast Cancer NOS 174.9
Abdominal Pain 789.07
Abscess Abdominal Wall 682.2
Hemorrhoids Int Bleedin 455.2
Malnutrition 263.9
Hernia Ing Unilat 550.90
SBO 560.81
GB Infl + stone + cystic obs 574.01
Cholecystitis Chronic 575.11
Thyroid B9 Neopl 226
Pancreatic Pseudocyst 577.2, Cholecystitis Chronic 575.11
GB Infl + stone + cystic obs 574.01
Cholecystitis Chronic 575.11
Debridement 11042, Debridement Fournier's 11004
Breast Bx Exc 19120
Lap SBR 44202
Lap Chole SILS 47562
I&D PRA 46040
Exp Lap 49000, LOA 44005, Small Bowel Resection 44120
Lap Chole 47562
Hernia Umb Inc 49587
Exp Lap 49000, LOA 44005, Enterostomy Repair 44602
Lap R Hemi 44205, Small Bowel Resection 44120
Lap R Hemi 44205
Lap HHR 43281
Lap VIHR + mesh 49655
Lap VIHR + mesh 49655
Lap Chole 47562
Lap VIHR + mesh 49655
Debridement Fournier's 11004
Exp Lap 49000, LOA 44005
LN Bx Superficial 38500
Lap R Hemi 44205
Breast Bx Inc 19101
Laparoscopy Diag 49320, Lap LOA 44180
PEG 49440
Hernia Ing Inc 49507
LOA 44005
Thyroidectomy Completion 60260
Panc Cystenterostomy 48520, Chole Open 47600
Lap Chole SILS 47562
Diverticulitis 562.11
Hernia Uni 550.90
Hernia Umbilical 553.1
Perirectal Abscess 566
Diverticulitis 577.0
553.21 Hernia Vental/Incisional
Abdominal Pain RUQ789.01
Abdominal Pain RLQ789.03
217: Benign Breast mass
541-appendicitis
Chronic 575.11 Cholecystitis
SBO560.81
786.05SOB
226/ Thyroid neoplasm

I need the output in column B to read:
707.03
611.72
560.81
574.20
566
560.81
574.20
553.1
560.81
555.9
211.3
553.3
553.21
553.21
577.0
789.01
789.01
575.11
553.21
608.83
789.04
196.5
153.4
174.9
789.07
682.2
455.2
263.9
550.90
560.81
574.01
575.11
226
577.2, 575.11
574.01
575.11
11042, 11004
19120
44202
47562
46040
49000, 44005, 44120
47562
49587
49000, 44005, 44602
44205, 44120
44205
43281
49655
49655
47562
49655
11004
49000, 44005
38500
44205
19101
49320, 44180
49440
49507
44005
60260
48520, 47600
47562
562.11
550.90
553.1
566
577.0
553.21
789.01
789.03
217
541
575.11
560.81
786.05
226

Notice that 550.90 is just that. I can't have 550.9 extracted.
Notice lines with multiple entries. Resultant are values separated by commas.
Text can be before or after the integer, never within the integer.

Thanks
Camil

[alansidman]

This might help you get started. Once you have the number extracted, you can apply the =Int formula to get only the integer. Your expected results show the entire number, not just the integer portion of the number. Do you really want only the integer?

http://office.microsoft.com/en-us/ex...001154901.aspx

Alan

[shg]

You could use a user-defined function.

Please Login or Register  to view this content.

E.g.,

=ExtractNums(A1, ", ")

Please Login or Register  to view this content.

[MarvinP]

Hi Camil and welcome to the forum,
I see from above that shg has provided a User Defined Function.

Here is another way without needing any VBA code.

Copy your column A into the windows clipboard and paste it into Notepad and then save that file as text. Then start excel and open the text file. It will ask if you want delimited or fixed length. You need to check Tab, Space and Comma to do the import. This will split your data into separate cells.

Then use the (long) formula supplied in the attached to get your desired results. See the attached Excel file and text file I used to show you how.