Search for number after specific text in string

[AMH]

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

 

[Mike H]

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&"0123456789")),LEN(A1))

or

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Mike

 

[AMH]

Hi Mike Thanks for the response, I can see what you are suggesting,
unfortunately I didn’t 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]

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]

No your right its the end of the 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

 

[Ron Rosenfeld]

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

 

[Mike H]

Hi,

try this

=IF(ISERROR(MID(A1,SEARCH("mpp",A1)+4,(SEARCH(",",A1))-(SEARCH("Mpp",A1)+4))),MID(A1,SEARCH("mpp",A1)+4,999),MID(A1,SEARCH("mpp",A1)+4,(SEARCH(",",A1))-(SEARCH("Mpp",A1)+4)))

Mike

 

[Teethless mama]

Try this:

=TRIM(MID(SUBSTITUTE(A1,",",REPT("
",99)),SEARCH("mpp\",SUBSTITUTE(A1,",",REPT(" ",99)))+4,99))

 

[AMH]

Thanks guys Mike's solution worked

Try this:

=TRIM(MID(SUBSTITUTE(A1,",",REPT("
",99)),SEARCH("mpp\",SUBSTITUTE(A1,",",REPT(" ",99)))+4,99))