| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for number after specific text in string
I want to search for a number after a specific piece of text in a string, the
number can be 1 to 4 characters in length and will finish with either a comma or a space, format like this : ..mpp\????, or .mpp\???? (with the question marks being numeric between 1 and 9999) Any help would be much appreciated |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search for number after specific text in string
Hi,
There are several ways of doing this so lets start with these =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),LEN(A1)) or =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))) Mike "AMH" wrote: I want to search for a number after a specific piece of text in a string, the number can be 1 to 4 characters in length and will finish with either a comma or a space, format like this : .mpp\????, or .mpp\???? (with the question marks being numeric between 1 and 9999) Any help would be much appreciated |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search for number after specific text in string
Hi Mike Thanks for the response, I can see what you are suggesting,
unfortunately I didnt give you enough information as there are numeric characters prior to the specific numeric I am looking for This is a sample of the complete string : \\ntf01\projects\Programme Plans\WAN\WAN.mpp\21,\\ntf01\projects\Programme Plans\WAN\WAN ADC Plan.mpp\5 The specific number I am looking for here is 21 as it is the first numeric after .mpp\ The string could also look like this : \\ntf01\projects\Programme Plans\WAN\WAN.mpp\21 And again I would look for the 21 "Mike H" wrote: Hi, There are several ways of doing this so lets start with these =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),LEN(A1)) or =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))) Mike "AMH" wrote: I want to search for a number after a specific piece of text in a string, the number can be 1 to 4 characters in length and will finish with either a comma or a space, format like this : .mpp\????, or .mpp\???? (with the question marks being numeric between 1 and 9999) Any help would be much appreciated |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search for number after specific text in string
Hi,
You need to clarify, In your first post you said the number is always followed by comma or space. In the second post you gave this example \\ntf01\projects\Programme Plans\WAN\WAN.mpp\21 Is there a space after 21? Mike "AMH" wrote: Hi Mike Thanks for the response, I can see what you are suggesting, unfortunately I didnt give you enough information as there are numeric characters prior to the specific numeric I am looking for This is a sample of the complete string : \\ntf01\projects\Programme Plans\WAN\WAN.mpp\21,\\ntf01\projects\Programme Plans\WAN\WAN ADC Plan.mpp\5 The specific number I am looking for here is 21 as it is the first numeric after .mpp\ The string could also look like this : \\ntf01\projects\Programme Plans\WAN\WAN.mpp\21 And again I would look for the 21 "Mike H" wrote: Hi, There are several ways of doing this so lets start with these =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),LEN(A1)) or =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))) Mike "AMH" wrote: I want to search for a number after a specific piece of text in a string, the number can be 1 to 4 characters in length and will finish with either a comma or a space, format like this : .mpp\????, or .mpp\???? (with the question marks being numeric between 1 and 9999) Any help would be much appreciated |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search for number after specific text in string
No your right its the end of the string
"Mike H" wrote: Hi, You need to clarify, In your first post you said the number is always followed by comma or space. In the second post you gave this example \\ntf01\projects\Programme Plans\WAN\WAN.mpp\21 Is there a space after 21? Mike "AMH" wrote: Hi Mike Thanks for the response, I can see what you are suggesting, unfortunately I didnt give you enough information as there are numeric characters prior to the specific numeric I am looking for This is a sample of the complete string : \\ntf01\projects\Programme Plans\WAN\WAN.mpp\21,\\ntf01\projects\Programme Plans\WAN\WAN ADC Plan.mpp\5 The specific number I am looking for here is 21 as it is the first numeric after .mpp\ The string could also look like this : \\ntf01\projects\Programme Plans\WAN\WAN.mpp\21 And again I would look for the 21 "Mike H" wrote: Hi, There are several ways of doing this so lets start with these =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),LEN(A1)) or =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))) Mike "AMH" wrote: I want to search for a number after a specific piece of text in a string, the number can be 1 to 4 characters in length and will finish with either a comma or a space, format like this : .mpp\????, or .mpp\???? (with the question marks being numeric between 1 and 9999) Any help would be much appreciated |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search for number after specific text in string
On Fri, 4 Sep 2009 03:26:01 -0700, AMH wrote:
I want to search for a number after a specific piece of text in a string, the number can be 1 to 4 characters in length and will finish with either a comma or a space, format like this : .mpp\????, or .mpp\???? (with the question marks being numeric between 1 and 9999) Any help would be much appreciated One way would be to download and install Longre's free morefunc.xll add-in (use Google to find a source), and then use this Regular Expression formula: =REGEX.MID(A1,"(?<=\.mpp\\)\d+") OR you could use this formula: =TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND( ".mpp\",A1)+5,255),","," ")," ",REPT(" ",99)),99)) --ron |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search for number after specific text in string
Hi,
try this =IF(ISERROR(MID(A1,SEARCH("mpp",A1)+4,(SEARCH(",", A1))-(SEARCH("Mpp",A1)+4))),MID(A1,SEARCH("mpp",A1)+4,9 99),MID(A1,SEARCH("mpp",A1)+4,(SEARCH(",",A1))-(SEARCH("Mpp",A1)+4))) Mike "AMH" wrote: No your right its the end of the string "Mike H" wrote: Hi, You need to clarify, In your first post you said the number is always followed by comma or space. In the second post you gave this example \\ntf01\projects\Programme Plans\WAN\WAN.mpp\21 Is there a space after 21? Mike "AMH" wrote: Hi Mike Thanks for the response, I can see what you are suggesting, unfortunately I didnt give you enough information as there are numeric characters prior to the specific numeric I am looking for This is a sample of the complete string : \\ntf01\projects\Programme Plans\WAN\WAN.mpp\21,\\ntf01\projects\Programme Plans\WAN\WAN ADC Plan.mpp\5 The specific number I am looking for here is 21 as it is the first numeric after .mpp\ The string could also look like this : \\ntf01\projects\Programme Plans\WAN\WAN.mpp\21 And again I would look for the 21 "Mike H" wrote: Hi, There are several ways of doing this so lets start with these =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),LEN(A1)) or =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))) Mike "AMH" wrote: I want to search for a number after a specific piece of text in a string, the number can be 1 to 4 characters in length and will finish with either a comma or a space, format like this : .mpp\????, or .mpp\???? (with the question marks being numeric between 1 and 9999) Any help would be much appreciated |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search for number after specific text in string
Try this:
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),SEARCH("mpp\",SUBSTITUTE(A1,",",REPT(" ",99)))+4,99)) "AMH" wrote: I want to search for a number after a specific piece of text in a string, the number can be 1 to 4 characters in length and will finish with either a comma or a space, format like this : .mpp\????, or .mpp\???? (with the question marks being numeric between 1 and 9999) Any help would be much appreciated |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search for number after specific text in string
Thanks guys Mike's solution worked
"Teethless mama" wrote: Try this: =TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),SEARCH("mpp\",SUBSTITUTE(A1,",",REPT(" ",99)))+4,99)) "AMH" wrote: I want to search for a number after a specific piece of text in a string, the number can be 1 to 4 characters in length and will finish with either a comma or a space, format like this : .mpp\????, or .mpp\???? (with the question marks being numeric between 1 and 9999) Any help would be much appreciated |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| search text string for number | Excel Discussion (Misc queries) | |||
| Find specific info using search string using VBA | Excel Discussion (Misc queries) | |||
| How to search Excel files based on a specific text string in VBA c | Excel Discussion (Misc queries) | |||
| Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
| search string for number value | Excel Discussion (Misc queries) | |||
Linear Mode
