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

 

[gilsa]

At this point it is most likely that after the number, if exists, the part will start with or contain the word sprint.
What I hardly believe might be changed at any certain point is the "\", which separates between the string parts.

 

[István Hirsch]

If you want to delete any substrings starting with Sprint after the last backslash, use this formula:

=TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND("\Sprint",A1&"\Sprint"),99,""),"\",REPT(" ",99)),99))

Similar result can be achieved without a formula, if you press Ctrl-H and replace „\Sprint*” with nothing (without quotation marks) then „*\” with nothing (if you want to keep the original, make a copy before replacing).

The formula below is more general, it deletes the last „\” with the following substring if the word „Sprint” appears anywhere in the cell:

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),(1+COUNTIF(A1,"*Sprint*"))*99),99))

 

[gilsa]

Works like a charm!

Many many thanks for your help.

 

[István Hirsch]

You are welcome, thanks for the feedback.