How to extract string from another string

gilsa

New Member
Joined
Sep 16, 2014
Messages
21
Hi All,
I'd highly appreciate to get some help on how to extract from a dynamic string length each time another part of it.
For instance the string might be:
1. \Sky\6.1.1
2. \Sky\6.1.1\6.1.10
3. \Sky\6.1.1\6.1.10\Sprint 1

If at the end the "Sprint 1" part exists then I need to fetch only 6.1.10
If not, the string from the right side until the "\" character (i.e either 6.1.1 in example 1 or 6.1.10 in example 2).

Many thanks in advance,
Gil
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
Assuming your first value is in A1, the following array formula:

=MID(SUBSTITUTE(A1,"\Sprint 1",""),MAX(IF(MID(SUBSTITUTE(A1,"\Sprint 1",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,"\Sprint 1","")))),1)="\",ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,"\Sprint 1",""))))))+1,LEN(SUBSTITUTE(A1,"\Sprint 1","")))

Confirm with CTRL+SHIFT+ENTER instead of just enter.
 

gilsa

New Member
Joined
Sep 16, 2014
Messages
21
Try this:

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"\Sprint 1",""),"\",REPT(" ",99)),99))


Hi István,
It works fine for the 2 first examples, however wherever the text "Sprint" is part of the string it returns the "Sprint 1" value rather the text between the 2 "\".
Just to make myself clear, if the string is "\Sky\6.1.1\6.1.10\Sprint 1", what I need to return is 6.1.10 and not "Sprint 1".

These are what I need:

If "\Sky\6.1.1\6.1.10\Sprint 1" return "6.1.10"
If "\Sky\6.1.1\6.1.10" return '6.1.10"
If ""\Sky\6.1.1" return 6.1.1
If ""\Sky" return "Sky"

Thanks again :)
 

gilsa

New Member
Joined
Sep 16, 2014
Messages
21

ADVERTISEMENT

Hi BiocideJ,
Same as I wrote to István, it works besides that if 'Sprint 1" exists in the string it returns that instead of the text before (the one between the 2 "\", "6.1.10").
Basically I am not sure if using the array formula will help me here, since I need to apply it to a dynamic table range using a macro.
Is it possible to do so in VBA using this:
ActiveCell.FormulaR1C1 = Your formula?
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
Istvan's solution is probably more efficient than mine so I would use that.

For either of our formulas, if the text being returned is not what you are asking for then the string you gave for the end "Sprint 1" cannot be the only thing you wish to exclude. Both of our formulas completely remove "/Sprint 1" so it would be impossible for them to return that value. Is it possible there is another type (e.g. no space "Sprint1") that should be removed?

Also, as an answer to your question about applying an array formula via VBA, the code would be
Code:
ActiveCell.FormulaArray = Your formula
 
Last edited:

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634

ADVERTISEMENT

It works fine for the 2 first examples, however wherever the text "Sprint" is part of the string it returns the "Sprint 1" value rather the text between the 2 "\".
Just to make myself clear, if the string is "\Sky\6.1.1\6.1.10\Sprint 1", what I need to return is 6.1.10 and not "Sprint 1".

These are what I need:

If "\Sky\6.1.1\6.1.10\Sprint 1" return "6.1.10"
If "\Sky\6.1.1\6.1.10" return '6.1.10"
If ""\Sky\6.1.1" return 6.1.1
If ""\Sky" return "Sky"

At me it works as requested in post #1. Please check if there is a space character in your strings at end position (delete if there is), also check why a „|” (pipe) character appears in your quote instead of the backslash I used. „|Sprint 1” will not be found, only „\Sprint 1”, so only the later can be substituted. If no difference is found, also check that the character between Sprint and 1 in your version is char(32) and not, for example, char(160).
Excel Workbook
AB
1\Sky\6.1.16.1.1
2\Sky\6.1.1\6.1.106.1.10
3\Sky\6.1.1\6.1.10\Sprint 16.1.10
4\SkySky
Sheet
 

gilsa

New Member
Joined
Sep 16, 2014
Messages
21
Hi István,
You are totally right, it did work for this specific example. The reason it didn't work for me, since as I mentioned (and maybe I wasn't clear enough, sorry for that) the string values might be changed and when I tested it the string contained at the end "Sprint 12".
Is there any option it will work in a dynamic string values which still has the same structure as in the 4 examples in your table?

*AB
1\Sky\6.1.16.1.1
2\Sky\6.1.1\6.1.106.1.10
3\Sky\6.1.1\6.1.10\Sprint 16.1.10
4\SkySky

<colgroup><col style="width:30px; "><col style="width:207px;"><col style="width:84px;"></colgroup><tbody>
</tbody>

Many thanks for your kindly support,
Gil
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Will the ending text if it exists always start with the word Sprint?
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
It seems possible, but how could you describe the patterns where the numbers separated by dots are followed by something else that is to be deleted: it is always Sprint followed by a number (Sprint 1, Sprint 4, Sprint 12 etc.) or can be any other word followed by a number? Perhaps several words followed by a number? Please try to collect each different pattern.
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,516
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top