Extract String

[gripper]

Hi Ladies and Gentlemen

I am trying to figure out a formula to extract the end of a string and move that result into another column

In column A I have file names like the following

C:\raw_keywords\Access_Import\Business\business_1_accounting.csv
C:\raw_keywords\Access_Import\Business\business_1_associations.csv
C:\raw_keywords\Access_Import\Business\business_1_business-to-business.csv

What I want to do is extract everything after the last "_" and put the results in column B
The results should be:

accounting.csv
associations.csv
business-to-business.csv

I would appreciate any assistance

Thank you very much!

 

[Rick Rothstein]

Give this formual a try...

=TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",999)),999))

 

[kpark91]

I'm having a bit of trouble understanding your formula.
I've tried copy + pasting, parts of code to see what's going on but I have no idea.

If you don't mind, could you show me how it worked please?

Much Thanks,
kpark.

 

[Rick Rothstein]

I'm having a bit of trouble understanding your formula.
I've tried copy + pasting, parts of code to see what's going on but I have no idea.

If you don't mind, could you show me how it worked please?

First off, you didn't say, but I'm assuming the formula worked for you, correct?

Okay, to explain how the formula works, let's use a smaller string, say, ABC_DEF and we'll use 9 instead of 999 for the constants. So, we have this...

A1: "ABC_DEF"
B1: =TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",9)),9))

Let us look at this part of the formula...

SUBSTITUTE(A1,"_",REPT(" ",9))

This results in the following...

"ABC         DEF"

where there are 9 space characters replacing the underline character. So, the RIGHT function calls part of the formula becomes this...

RIGHT("ABC         DEF",9)

which returns the 9 right most character, those being...

"      DEF"

where there are 6 space characters in front of the letters. The final TRIM function call remove the outer blanks leaving just the DEF which are all the character there were after the underline character. Now, for the original formula, I used 999 because I needed a number of space characters that were larger than the maximum possible size of a path string in Windows (260 for a normal path I believe). Theoretically, I could have used 260 instead of 999, but 999 is just easier to type and it does not place that much of an extra burden on Excel to exeute with it. I would note, though, this formula could fail if your path or filenames contained multiple adjacent blanks as part of the text (the TRIM function would collapse them to a single space character). If there could be multiple consecutive blanks in the text, then use this formula instead...

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A14," ",CHAR(1)),"/",REPT(" ",99)),99)),CHAR(1)," ")

 

[vijayendra.shukla]

This might be useful and will help you write the formula from scratch with proper understanding. I liked it very much. Read the
Extract the last token in a cell

section.

Link: http://www.dailydoseofexcel.com/

 

[gripper]

I was the original poster of this question and it worked wonderfully.

Thank you very much

 

[Rick Rothstein]

I was the original poster of this question and it worked wonderfully.

Sorry I mistook that other poster for you... I didn't bother to check as his message sounded just like a follow-up would sound from the original poster.

 

[kpark91]

Thank you for the explanation!