CODEWITHSUNDEEP
excelexcel-formula
drunkenvash
drunkenvash

Reputation: 37

How to extract a string from a cell from right to left using a specific character that occurs more than once?

Normally I would just use the RIGHT function in excel to split it by finding a specific character such as / and outputting the string that I want.

However, I am finding trouble extracting THISSTRING.txt from d/aaa/THISSTRING.txt. With only one instance of / I would just use a function such as =RIGHT(B17,LEN(B17) - FIND("/",B17))

enter image description here

Upvotes: 0

Views: 824

Answers (3)

barry houdini
barry houdini

Reputation: 46371

Here's another way.....

=REPLACE(B17,1,LOOKUP(2^15,FIND("/",B17,ROW(INDIRECT("1:"&LEN(B17))))),"")

FIND generates an array of values, indicating the first position of a "/" in B17, but with the start point incrementing by 1 each time, which means that the last numeric value in that array is the positon of the last "/".

LOOKUP extracts that value from the array and we can use it in REPLACE function to replace all characters before and at that position with nothing, just leaving you with all characters after the last "/"

You'll get an error if there is no "/" in B17

Upvotes: 0

EEM
EEM

Reputation: 6659

Objective: To return the rightmost sub-string from a target string after the last occurrence of a character which appears several times within the target string.

This formula:

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99))

Provides the correct result under the following conditions:

  1. The sub-string to retrieve does not have more than 99 characters.
  2. The sub-string to retrieve does not contain more than one space character together.

Example: To retrieve a sub-string which is 123 characters long and contains the following characters 1 ABC XXX 123 XYZ.

Point 1 is easily solved by working with the length of the string instead of a fixed number:

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))), LEN(A1)))

However point 2 can't be overcome with the referred formula.

Proposed solution: The following formula returns the correct result regardless of the conditions mentioned above:

=RIGHT(A1, LEN(A1) - FIND( CHAR(12),
SUBSTITUTE(A1, "/", CHAR(12), LEN(A1) - LEN( SUBSTITUTE(A1, "/", "" )))))

Note: I used non-printable character 12 which is very unlikely to be found in excel, change as required.

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

Here's one way to get the rightmost:

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99))

Upvotes: 2

Related Questions