| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search
Is it possible to use the Search (or Find) function to find a number in a
text string? eg SEARCH(CODE(<57),A1) This doesn't work, but hopefully it gives an idea of what I'm after. Ideally, I'd like: SEARCH(CODE(48 to 57),A1) Possible? Regards - Dave. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search
SEARCH will return the starting position number of the search criteria. Is
that what you want? How about posting several representative samples of your data so we can what you're trying to do. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Is it possible to use the Search (or Find) function to find a number in a text string? eg SEARCH(CODE(<57),A1) This doesn't work, but hopefully it gives an idea of what I'm after. Ideally, I'd like: SEARCH(CODE(48 to 57),A1) Possible? Regards - Dave. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search
OK,
Ex.1 qwertyabc1234567 Ex.2 poi4567365fgher Ex.3 6945908FGTEy I would like to be able to extract just the numerical characters from each cell. The numerical part is always 7 characters long, but I don't know where it begins in the string, or how many non-numerical characters there may be. So I want to search for the first number (SEARCH(CODE(48 to 57), then use that as the starting point for the MID() function, which can extract the 7 digit number if it knows where to start. Sorry I was vague before. Hope this is better. Regards - Dave. "T. Valko" wrote: SEARCH will return the starting position number of the search criteria. Is that what you want? How about posting several representative samples of your data so we can what you're trying to do. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Is it possible to use the Search (or Find) function to find a number in a text string? eg SEARCH(CODE(<57),A1) This doesn't work, but hopefully it gives an idea of what I'm after. Ideally, I'd like: SEARCH(CODE(48 to 57),A1) Possible? Regards - Dave. . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search
The numerical part is always 7 characters long
Try this... =LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7)) Note that this formula will strip off any leading 0s from the number string. Excel doesn't recognize leading 0s as part of a *numeric number*. If there might be leading 0s you can either use a different formula to extract the string as a TEXT value and retain any leading 0s or you can use the above formula which extracts the string as a NUMERIC NUMBER and then apply a custom number format to display any leading 0s. -- Biff Microsoft Excel MVP "Dave" wrote in message ... OK, Ex.1 qwertyabc1234567 Ex.2 poi4567365fgher Ex.3 6945908FGTEy I would like to be able to extract just the numerical characters from each cell. The numerical part is always 7 characters long, but I don't know where it begins in the string, or how many non-numerical characters there may be. So I want to search for the first number (SEARCH(CODE(48 to 57), then use that as the starting point for the MID() function, which can extract the 7 digit number if it knows where to start. Sorry I was vague before. Hope this is better. Regards - Dave. "T. Valko" wrote: SEARCH will return the starting position number of the search criteria. Is that what you want? How about posting several representative samples of your data so we can what you're trying to do. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Is it possible to use the Search (or Find) function to find a number in a text string? eg SEARCH(CODE(<57),A1) This doesn't work, but hopefully it gives an idea of what I'm after. Ideally, I'd like: SEARCH(CODE(48 to 57),A1) Possible? Regards - Dave. . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search
Improvement...
=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7)) In this case, since the number is a specific length the LOOKUP function is redundant. --MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... The numerical part is always 7 characters long Try this... =LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7)) Note that this formula will strip off any leading 0s from the number string. Excel doesn't recognize leading 0s as part of a *numeric number*. If there might be leading 0s you can either use a different formula to extract the string as a TEXT value and retain any leading 0s or you can use the above formula which extracts the string as a NUMERIC NUMBER and then apply a custom number format to display any leading 0s. -- Biff Microsoft Excel MVP "Dave" wrote in message ... OK, Ex.1 qwertyabc1234567 Ex.2 poi4567365fgher Ex.3 6945908FGTEy I would like to be able to extract just the numerical characters from each cell. The numerical part is always 7 characters long, but I don't know where it begins in the string, or how many non-numerical characters there may be. So I want to search for the first number (SEARCH(CODE(48 to 57), then use that as the starting point for the MID() function, which can extract the 7 digit number if it knows where to start. Sorry I was vague before. Hope this is better. Regards - Dave. "T. Valko" wrote: SEARCH will return the starting position number of the search criteria. Is that what you want? How about posting several representative samples of your data so we can what you're trying to do. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Is it possible to use the Search (or Find) function to find a number in a text string? eg SEARCH(CODE(<57),A1) This doesn't work, but hopefully it gives an idea of what I'm after. Ideally, I'd like: SEARCH(CODE(48 to 57),A1) Possible? Regards - Dave. . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search
Hi Biff,
Thanks - perfect. I haven't seen this use of FIND, either the curly bracket part, or the A1&"0123456789" Could you explain that please, especially the 2nd part. Regards - Dave. PS, I'm not getting notified of replies in my email, even though I tick the box. Is that happening to everyone, or just me? "T. Valko" wrote: Improvement... =LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7)) In this case, since the number is a specific length the LOOKUP function is redundant. --MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... The numerical part is always 7 characters long Try this... =LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),7)) Note that this formula will strip off any leading 0s from the number string. Excel doesn't recognize leading 0s as part of a *numeric number*. If there might be leading 0s you can either use a different formula to extract the string as a TEXT value and retain any leading 0s or you can use the above formula which extracts the string as a NUMERIC NUMBER and then apply a custom number format to display any leading 0s. -- Biff Microsoft Excel MVP "Dave" wrote in message ... OK, Ex.1 qwertyabc1234567 Ex.2 poi4567365fgher Ex.3 6945908FGTEy I would like to be able to extract just the numerical characters from each cell. The numerical part is always 7 characters long, but I don't know where it begins in the string, or how many non-numerical characters there may be. So I want to search for the first number (SEARCH(CODE(48 to 57), then use that as the starting point for the MID() function, which can extract the 7 digit number if it knows where to start. Sorry I was vague before. Hope this is better. Regards - Dave. "T. Valko" wrote: SEARCH will return the starting position number of the search criteria. Is that what you want? How about posting several representative samples of your data so we can what you're trying to do. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Is it possible to use the Search (or Find) function to find a number in a text string? eg SEARCH(CODE(<57),A1) This doesn't work, but hopefully it gives an idea of what I'm after. Ideally, I'd like: SEARCH(CODE(48 to 57),A1) Possible? Regards - Dave. . . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search
Dave
'to return the first position of a numeric within the text =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") ) 'So for your data =--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),7) -- Jacob "Dave" wrote: Is it possible to use the Search (or Find) function to find a number in a text string? eg SEARCH(CODE(<57),A1) This doesn't work, but hopefully it gives an idea of what I'm after. Ideally, I'd like: SEARCH(CODE(48 to 57),A1) Possible? Regards - Dave. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search
Thanks Jacob. Perfect.
Regards - Dave. "Jacob Skaria" wrote: Dave 'to return the first position of a numeric within the text =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") ) 'So for your data =--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),7) -- Jacob "Dave" wrote: Is it possible to use the Search (or Find) function to find a number in a text string? eg SEARCH(CODE(<57),A1) This doesn't work, but hopefully it gives an idea of what I'm after. Ideally, I'd like: SEARCH(CODE(48 to 57),A1) Possible? Regards - Dave. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| file search or search files | Excel Discussion (Misc queries) | |||
| Functions (search within search result) reply to this please | Excel Worksheet Functions | |||
| Functions (search within search result) | Excel Worksheet Functions | |||
| Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Charts and Charting in Excel | |||
| How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Hybrid Mode
