Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to Extract text BETWEEN 1st-2nd-3rd-nth char.

0

Hi,

I watched this tutorial and am curious if it is possible to expand upon this to extract specific text between two nth occurances of the "X" character?

https://www.teachexcel.com/excel-tutorial/2211/extract-text-before-or-after-the-1st-2nd-3rd-nth-delimiter-in-excel?nav=yt

I have a nasty data output file with a large string of user names separated by a semi-colon, and the last name is always the main user's name (this is for approval history of some data, last name always being self approver once all other approvals are done); so I need to find the second to last name in this string of text.

Attached sample file, I need to extract the 2nd to last (as the number of approvers varies) name in column J, names are separated by a semi-colon.  The last name in column J will always equal that in Column D, so the desired result would be (in this example) the name hardcoded into column L.

Thanks in advance!

excel
MS Office Version: Mac16
posted by: JBagwell (rep: 2) on Tue Sep 19, 2023 at 1:43 pm
Answer
Discuss

Answers

0
Selected Answer

Hi Jeremy and welcome to the Forum.

You can use the SUBSITUTE function with others to do that. Given your delimiter is the semi-colon (;), in the first attached file the formula in (green) cell K2 is:

=TRIM(MID(SUBSTITUTE(";"&J2,";",REPT(" ",100)),1+100*(LEN(J2)-LEN(SUBSTITUTE(J2,";",""))),100))

and produces Voievutska_Alisa as required. That's copied down to K3 too.

Revision 20 Sepember 2023

Further to the user's discussion point raised below, odd results arise if the target string is over 100 characters (since the formula relies on the string being less to detect the next to last string). This can be corrected by changing 100 to 1000 in the formula above.

Furthermore, if there are target strings without the ";" delimiter (i.e. a single owner/approver), a blank string is returned by the revised (1000) formula. That can be catered for by the combination of an IF statmement with a FIND function, as below...

In the second attached file below, the formula in green cell K2 is changed (in bold) to:

=IF(ISERROR(FIND(";",J2)),J2, TRIM(MID(SUBSTITUTE(";"&J2,";",REPT(" ",1000)),1+1000*(LEN(J2)-LEN(SUBSTITUTE(J2,";",""))),1000)))

That's copied down to K9 (apart from K4) and produces the right result.

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.

posted by: John_Ru (rep: 6152) on Tue Sep 19, 2023 at 6:40 pm
Discuss

Discussion

Just spotted your Excel version is Mac16- I have no experience of that so hope the PC formula works with that too.
John_Ru (rep: 6152) Sep 19, '23 at 7:10 pm
Glad that helped. Thanks for selecting my Answer, Jeremy 
John_Ru (rep: 6152) Sep 19, '23 at 7:34 pm
Hi, thank you for the help, however, this actually only partially works, I have updated the sample file I originally posted, lines 5 through 9 are showing real data, with the provided formula, and some notes in column L.  Some of the results the formula provided are giving make no sense..?  Any further insight would be greatly appreciated!
JBagwell (rep: 2) Sep 20, '23 at 11:20 am
Mm. I'll look at your revised file when I'm back at my PC, some time tomorrow hopefully. 
John_Ru (rep: 6152) Sep 20, '23 at 12:56 pm
Jeremy, please see revised answer and second file.
John_Ru (rep: 6152) Sep 20, '23 at 6:17 pm
Hi, John, thanks a million, this works great - cheers!
JBagwell (rep: 2) Sep 21, '23 at 10:33 am
Thanks again for (re)selecting my Answer, Jeremy. 
John_Ru (rep: 6152) Sep 21, '23 at 1:04 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login