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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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 :)
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Will the ending text if it exists always start with the word Sprint?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,217,257
Messages
6,135,503
Members
449,944
Latest member
parag385

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
Back
Top