How to extract string from another string

[gilsa]

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

 

[BiocideJ]

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.

 

[István Hirsch]

Try this:

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

 

[gilsa]

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]

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]

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

ActiveCell.FormulaArray = Your formula

 

[István Hirsch]

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
	A	B
1	\Sky\6.1.1	6.1.1
2	\Sky\6.1.1\6.1.10	6.1.10
3	\Sky\6.1.1\6.1.10\Sprint 1	6.1.10
4	\Sky	Sky
Sheet

 

[gilsa]

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?

*	A	B
1	\Sky\6.1.1	6.1.1
2	\Sky\6.1.1\6.1.10	6.1.10
3	\Sky\6.1.1\6.1.10\Sprint 1	6.1.10
4	\Sky	Sky

<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]

Will the ending text if it exists always start with the word Sprint?

 

[István Hirsch]

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.