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

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

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

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

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

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

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

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

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