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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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,955
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,331
Messages
5,528,052
Members
409,800
Latest member
camronmartin

This Week's Hot Topics

Top