TextJoin Function

[Kathryn_Marie]

Hi All,
I need to create a folder path from a raw data source which will update often. The data is on this format:

Material (SAP L.1-5 Functional Process Hierarchy)
	_Simulation Guide
	_eLearning Guide
		_Title Page
		_Audience and Prerequisites
		_Housekeeping
		_Topics
		Topic 1 - <Title>
			_Topic
			_Objectives
			_Introduction
			_Simulation - <Title>
		_Conclusion

I have used this formula which almost works, but it keeps adding previous entries from the same column which I do not want. Wondering is anyone may know how I can use this formula till it finds an entry, then starts again, or if there is another way to do this. This is what I have which gives the result below:

=TEXTJOIN("/", TRUE, E$2:E2, F$2:F2, G$2:G2, H$2:H2, I$2:I2, J$2:J2)

Content

Content/Material (SAP L.1-5 Functional Process Hierarchy)

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Topic

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Topic/_Objectives

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Topic/_Objectives/_Introduction

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Topic/_Objectives/_Introduction/_Simulation - <Title>

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Conclusion/_Topic/_Objectives/_Introduction/_Simulation - <Title>

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Quiz Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Conclusion/_Topic/_Objectives/_Introduction/_Simulation - <Title>

 

[Peter_SSs]

Sticking to your original request for a TEXTJOIN solution, would this adaptation of Ron's initial formula work for you?

A_MrExcel.xlsm
	D	E	F	G	H	I
2	Material (SAP L.1-5 Functional Process Hierarchy)					Material (SAP L.1-5 Functional Process Hierarchy)
3		_Simulation Guide				Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide
4		_eLearning Guide				Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide
5			_Title Page			Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Title Page
6			_Audience and Prerequisites			Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Audience and Prerequisites
7			_Housekeeping			Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Housekeeping
8			_Topics			Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Topics
9			Topic 1 - <Title>			Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>
10				_Topic		Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Topic
11				_Objectives		Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Objectives
12				_Introduction		Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Introduction
13				_Simulation - <Title>		Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Simulation - <Title>
14			_Conclusion			Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Conclusion
Folder Path

Cell Formulas
Range	Formula
I2:I14	I2	=TRIM(LEFT(SUBSTITUTE(TEXTJOIN("/",TRUE,LOOKUP("ZZZ",D$2:D2),IFERROR(LOOKUP("ZZZ",E$2:E2),""),IFERROR(LOOKUP("ZZZ",F$2:F2),""),IFERROR(LOOKUP("ZZZ",G$2:G2),"")),"/",REPT(" ",999),MATCH("?*",C2:G2,0)-1),999))

 

[Kathryn_Marie]

I would definitely much prefer this option, however I updated your code to match what I required in my spreadsheet, and I am just getting the value from column F, nothing else :/

=TRIM(LEFT(SUBSTITUTE(TEXTJOIN("/",TRUE,LOOKUP("ZZZ",F$2:F2),IFERROR(LOOKUP("ZZZ",G$2:G2),""),IFERROR(LOOKUP("ZZZ",H$2:H2),""),IFERROR(LOOKUP("ZZZ",I$2:I2),""),IFERROR(LOOKUP("ZZZ",J$2:J2),""),IFERROR(LOOKUP("ZZZ",K$2:K2),"")),"/",REPT(" ",999),MATCH("?*",C2:K2,0)-1),999))

 

[Peter_SSs]

Hard to tell exactly without seeing your data and layout, but I did have something not quite ideal in my formula.
If this does not resolve the problem, please post a small set of sample data and expected results with XL2BB so that we can
a) See exactly what rows & columns the data is in
b) See what column the formula is in, and
b) Copy the data to our sheet for testing

A_MrExcel.xlsm
	F	G	H	I	J	K	L
1
2	Material (SAP L.1-5 Functional Process Hierarchy)						Material (SAP L.1-5 Functional Process Hierarchy)
3		_Simulation Guide					Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide
4		_eLearning Guide					Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide
5			_Title Page				Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Title Page
6			_Audience and Prerequisites				Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Audience and Prerequisites
7			_Housekeeping				Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Housekeeping
8			_Topics				Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Topics
9			Topic 1 - <Title>				Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>
10				_Topic			Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Topic
11				_Objectives			Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Objectives
12				_Introduction			Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Introduction
13				_Simulation - <Title>			Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Simulation - <Title>
14			_Conclusion				Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Conclusion
Folder Path

Cell Formulas
Range	Formula
L2:L14	L2	=TRIM(LEFT(SUBSTITUTE(TEXTJOIN("/",TRUE,LOOKUP("ZZZ",F$2:F2),IFERROR(LOOKUP("ZZZ",G$2:G2),""),IFERROR(LOOKUP("ZZZ",H$2:H2),""),IFERROR(LOOKUP("ZZZ",I$2:I2),""),IFERROR(LOOKUP("ZZZ",J$2:J2),""),IFERROR(LOOKUP("ZZZ",K$2:K2),"")),"/",REPT(" ",999),MATCH("?*",F2:K2,0)),999))

 

[Kathryn_Marie]

Yes, that worked!

Thanks to the both of you for your help

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.