0

i have got an Excel sheet with over than 8000 row and 15 columns i want a function to remove all the letters and special characters from a cell and only leave the numbers

DFM-TEL-CIS-1025 --> 1025

DFM-VTM-1299 --> 1299

TEL-CIV-DWG-DRX-1021 --> 3021

Mohannad Al Amad
  • 1
  • 1
  • 4
  • Can there be multiple number groups in a cell (e.g. BOB-123-CAT-456) and how should they end up? Are the numbers always at the end? And presumably the last result is a typo _--> 1021_ – Captain Aug 18 '14 at 07:42
  • Here is an article with an example using array formulas [link](http://office.microsoft.com/en-ca/excel-help/extracting-numbers-from-alphanumeric-strings-HA001154901.aspx). You'd need to test for speed as running it against 120000 cells could be pretty slow! – Captain Aug 18 '14 at 08:09
  • @Captain: Yes, but depending on the answers to your original questions we may well be able to do without an array formula set-up. – XOR LX Aug 18 '14 at 08:12
  • @Captain I'm going to give you a real example DFM-1086-CIV-DWG-REW-001 -> 001 /// DFM-1086-CIV-DWG-TEL-093 -> 093 /// DFM-1086-ELECT-556 -> 556 /// DFM-1086-CIV-DRG-1732 -> 1732 /// – Mohannad Al Amad Aug 18 '14 at 09:28
  • @Captain yes i have a number in the meddle but i only want the last 3 to 4 numbers – Mohannad Al Amad Aug 18 '14 at 09:30

1 Answers1

0

This will give the result you are looking for

=IF(MID(C4,LEN(C4)-3,1)="-",RIGHT(C4,3),RIGHT(C4,4))

Where C4 is the cell containing the data.

This function assumes that the last 3 or 4 characters in the string are always numeric and that the character just before them is a hyphen.

Tai Paul
  • 900
  • 10
  • 19