Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting numbers from alphanumeric strings
SSHRC856-2004-0033 Bielawski
SSHRC858-20-0027Nadasdi SSHRC858-04-0001Daveluy SSHRC856-04-010 Blair,H SSHRC421-2005-03 Haggerty SSHRC861-2005-025 Garvin For e.g from above information is in Cell A, I want to have only no. like from ISt 856-2004-0033, Could it possible by signle line formula. Thanks in advance Poonam |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting numbers from alphanumeric strings
Try going to Data---Text-to-Columns
Click on Fixed Width and move the line between the letters and where the numbers start. Then click finish. "Poonam" wrote: SSHRC856-2004-0033 Bielawski SSHRC858-20-0027Nadasdi SSHRC858-04-0001Daveluy SSHRC856-04-010 Blair,H SSHRC421-2005-03 Haggerty SSHRC861-2005-025 Garvin For e.g from above information is in Cell A, I want to have only no. like from ISt 856-2004-0033, Could it possible by signle line formula. Thanks in advance Poonam |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting numbers from alphanumeric strings
Hi Poonam
Assuming all your strings start with 5 letters and the number strings are always followed by a space: =MID(A1,6,FIND(" ",A1)-6) would work for a string in A1. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Poonam" wrote in message ... SSHRC856-2004-0033 Bielawski SSHRC858-20-0027Nadasdi SSHRC858-04-0001Daveluy SSHRC856-04-010 Blair,H SSHRC421-2005-03 Haggerty SSHRC861-2005-025 Garvin For e.g from above information is in Cell A, I want to have only no. like from ISt 856-2004-0033, Could it possible by signle line formula. Thanks in advance Poonam |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting numbers from alphanumeric strings
Try this based on the following assumptions:
There is *always* 2 hyphens in the cell and the hyphens are *always* part of the number string. The number string *always* starts at the 6th character. Enter this array formula** : =MID(A1,6,COUNT(--MID(A1,ROW(INDIRECT("6:100")),1))+2) -- Biff Microsoft Excel MVP "Poonam" wrote in message ... SSHRC856-2004-0033 Bielawski SSHRC858-20-0027Nadasdi SSHRC858-04-0001Daveluy SSHRC856-04-010 Blair,H SSHRC421-2005-03 Haggerty SSHRC861-2005-025 Garvin For e.g from above information is in Cell A, I want to have only no. like from ISt 856-2004-0033, Could it possible by signle line formula. Thanks in advance Poonam |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting numbers from alphanumeric strings
On Fri, 4 Apr 2008 14:41:00 -0700, Poonam
wrote: SSHRC856-2004-0033 Bielawski SSHRC858-20-0027Nadasdi SSHRC858-04-0001Daveluy SSHRC856-04-010 Blair,H SSHRC421-2005-03 Haggerty SSHRC861-2005-025 Garvin For e.g from above information is in Cell A, I want to have only no. like from ISt 856-2004-0033, Could it possible by signle line formula. Thanks in advance Poonam This will return all the numbers and hyphens beginning with the first digit and ending with the first character that is not a digit or hyphen: =TRIM(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789")),MIN(SEARCH( {"a","b","c","d","e","f","g","h","i","j","k","l"," m","n","o","p","q","r","s","t","u","v","w","x","y" ,"z"}, A1&"abcdefghijklmnopqrstuvwxyz",MIN(SEARCH({0,1,2, 3,4,5,6,7,8,9}, A1&"0123456789"))))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) )) --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting numbers from alphanumeric strings
Hi Poonam,
Here's a more flexible formula to strip out all leading and trailing non-numerics from a cell: =MID(A1,MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),"")),MAX(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),""))-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),""))+1) This is an array formula, so you input it with Ctrl-Shift-Enter, after which it will ben enclosed in a pair of braces (ie '{}'). Cheers -- macropod [MVP - Microsoft Word] ------------------------- "macropod" wrote in message ... Hi Poonam Assuming all your strings start with 5 letters and the number strings are always followed by a space: =MID(A1,6,FIND(" ",A1)-6) would work for a string in A1. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Poonam" wrote in message ... SSHRC856-2004-0033 Bielawski SSHRC858-20-0027Nadasdi SSHRC858-04-0001Daveluy SSHRC856-04-010 Blair,H SSHRC421-2005-03 Haggerty SSHRC861-2005-025 Garvin For e.g from above information is in Cell A, I want to have only no. like from ISt 856-2004-0033, Could it possible by signle line formula. Thanks in advance Poonam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
separating numbers from a alphanumeric cell | Excel Worksheet Functions | |||
extracting numbers from alphanumeric cells | Excel Worksheet Functions | |||
how to extract decimal numbers from alphanumeric strings in Excel | Excel Discussion (Misc queries) | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
2 more questions about extracting numbers from text strings | Excel Discussion (Misc queries) |