Ask Experts Questions for FREE Help !
Ask
    winningeleven9's Avatar
    winningeleven9 Posts: 2, Reputation: 1
    New Member
    		  
    #1

    Jan 18, 2010, 07:16 PM
    (Microsoft Excel) - Need to extract a specific number from text string
    For example, cell K1 looks like this

    Z. Thornton(90), J. Bornstein(90), A. Jazic(65), C. Talley(90), Y. Cuesta(90), S. Kljestan(90), P. Nagamura(90), M. Lahoud(90), J. Braun(90), J. Padilla(46), Maicon Santos(60)

    I want a formula that will give me the number to the right of a name if the cell contains that name. So something like "if K1 contains A. Jazic then find the number to the right of his name (65) without the brackets, if not, leave the cell blank

    Thanks!
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #2

    Jan 19, 2010, 12:37 PM

    If K1 holds your string as shown above...
    If A1 holds the name string to match...
    If the numbers are all 2-digits...

    Put this formula in B1:
    =IF(ISNUMBER(SEARCH(A1, K1)), MID(K1, FIND("(", K1, SEARCH(A1, K1)) + 1, 2), "")
    winningeleven9's Avatar
    winningeleven9 Posts: 2, Reputation: 1
    New Member
    		  
    #3

    Jan 19, 2010, 05:06 PM
    Quote Originally Posted by JBeaucaire View Post
    If K1 holds your string as shown above...
    If A1 holds the name string to match...
    If the numbers are all 2-digits...

    Put this formula in B1:
    =IF(ISNUMBER(SEARCH(A1, K1)), MID(K1, FIND("(", K1, SEARCH(A1, K1)) + 1, 2), "")
    That works! Thanks!
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Excel 15 digit issue, tried converting to text, text to column feature negates fix [ 6 Answers ]

I have the following numbers that exceed 15 characters that needs to be split into its own columns. Down the road, there would be thousands of such rows of data with the first couple set of unique numbers. 890432453253208820,5004500558,05CC,1,0,0,0,0,0,0, 0000,5.0000,2007-01-11...

I received a text messege from this number +639103685415. How to trace this number? [ 3 Answers ]

I received a text messege from this number +639103685415. How to trace this number?

Excel: extract surname from column with first name and sometimes initials [ 1 Answers ]

In an Excel spreadsheet I have a column containing first name, sometimes an initial (or two), then a surname then in brackets a date of birth. How can I extract the surname? e.g. one row might be John A Smith (01/01/1990) The next might be John A Smith (02/02/1991) and the next John Smith...

SQL Expression to extract part of a string divided by dashes [ 4 Answers ]

Hi, I have a field (Tag_Num) in an Access database that contains a heap of values that look something like this; 12345-1234-ABC-40-CG1 12345-2868-ACC-40-CG1 12345-4897-ACC-40-CG1 12345-7874-BGC-40-CG1 12345-8756-BXC-40-CG1 12348-123-ABC-40-CG1

View more questions Search