Adding Characters to a line of information

[esb128]

Hello,

I need help creating a formula to insert the following data between the large spaces of information i have in multiple lines below *** Please note there are about 5 spaces between these sentances, but when i save it the website removes the extra spaces.

Problem

Positive seal sealing and holding is accomplished instantaneously Reusable connect and disconnect numerous times Full flow fitting seals on outside diameter of tubing Internal hex allows ease of assembly in tight places Pre-applied thread sealant

Answer

Positive seal sealing and holding is accomplished instantaneously</li><li>Reusable connect and disconnect numerous times</li><li>Full flow fitting seals on outside diameter of tubing</li><li>Internal hex allows ease of assembly in tight places</li><li>Pre-applied thread sealant

 

[duggie33]

Hi,

What do you mean by "the large spaces of information"?

If you could provide some sample data and expected results, it will be easier for us to help you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that this board also has a "Test Here” forum. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks,

Doug

 

[esb128]

Thanks, i am having trouble with the xl2bb, to make it more simple, every new sentance in the data has a capitalized letter, can it be programmed to insert the </li><li> before each capitalized letter?

 

[esb128]

Hello, I am revising the wording of my ask, which should make it more simple

Hello,

I need help creating a formula to insert an </li><li> in the following data before each capitalized word in the sentence below, it must skip the first capitalized word in the sentence.

Data

Positive seal sealing and holding is accomplished instantaneously Reusable connect and disconnect numerous times Full flow fitting seals on outside diameter of tubing Internal hex allows ease of assembly in tight places Pre-applied thread sealant

Result

Positive seal sealing and holding is accomplished instantaneously</li><li>Reusable connect and disconnect numerous times</li><li>Full flow fitting seals on outside diameter of tubing</li><li>Internal hex allows ease of assembly in tight places</li><li>Pre-applied thread sealant

 

[duggie33]

I am not sure that your question is different...

I have a solution that would require helper at least one helper column to locate the "space" before the capital letters. This single helper column would require combining columns B through G in my example below; that is not too difficult with a LET formula. The part I cannot get my head around is multiple REPLACES within a string. Can this be done with a single formula that identifies the positions to be replaced?

Book1
	A	B	C	D	E	F	G	H	I
1	Positive seal sealing and holding is accomplished instantaneously Reusable connect and disconnect numerous times Full flow fitting seals on outside diameter of tubing Internal hex allows ease of assembly in tight places Pre-applied thread sealant	Broken down into Single Letters	Ascii Codes	Capital Letters		Position	Caps from column D	Position of Space before Caps	Space replaced with specified text; one at a time; is there a way to do this with a single formula?
2		P	80	1		1	1	220	Positive seal sealing and holding is accomplished instantaneously Reusable connect and disconnect numerous times Full flow fitting seals on outside diameter of tubing Internal hex allows ease of assembly in tight places</li><li>Pre-applied thread sealant
3		o	111	0		2	0	167	Positive seal sealing and holding is accomplished instantaneously Reusable connect and disconnect numerous times Full flow fitting seals on outside diameter of tubing</li><li>Internal hex allows ease of assembly in tight places</li><li>Pre-applied thread sealant
4		s	115	0		3	0	113	Positive seal sealing and holding is accomplished instantaneously Reusable connect and disconnect numerous times</li><li>Full flow fitting seals on outside diameter of tubing</li><li>Internal hex allows ease of assembly in tight places</li><li>Pre-applied thread sealant
5		i	105	0		4	0	66	Positive seal sealing and holding is accomplished instantaneously</li><li>Reusable connect and disconnect numerous times</li><li>Full flow fitting seals on outside diameter of tubing</li><li>Internal hex allows ease of assembly in tight places</li><li>Pre-applied thread sealant
6		t	116	0		5	0
7		i	105	0		6	0
8		v	118	0		7	0
9		e	101	0		8	0
10			32	0		9	0
11		s	115	0		10	0
12		e	101	0		11	0
13		a	97	0		12	0
14		l	108	0		13	0
15			32	0		14	0
16		s	115	0		15	0
17		e	101	0		16	0
18		a	97	0		17	0
19		l	108	0		18	0
20		i	105	0		19	0
21		n	110	0		20	0
22		g	103	0		21	0
23			32	0		22	0
24		a	97	0		23	0
25		n	110	0		24	0
26		d	100	0		25	0
27			32	0		26	0
Sheet1

Cell Formulas
Range	Formula
B2:B247	B2	=MID($A$1,ROW(INDIRECT("$A$1:$A$"&LEN($A$1))),1)
C2:C247	C2	=CODE(MID($A$1,ROW(INDIRECT("$A$1:$A$"&LEN($A$1))),1))
D2:D247	D2	=BYROW(C2#,LAMBDA(x,IF(AND(x>=65,x<=90),1,0)))
F2:G247	F2	=HSTACK(SEQUENCE(LEN(A1)),D2#)
H2:H5	H2	=SORT(FILTER(INDEX(F2#,,1),(INDEX(F2#,,1)>1)*(INDEX(F2#,,2)=1))-1,,-1)
I2	I2	=REPLACE($A$1,H2,1,"</li><li>")
I3:I5	I3	=REPLACE(I2,H3,1,"</li><li>")
Dynamic array formulas.

Maybe some has a way to do the REPLACE in a single formula...

Hope that helps get it headed in the right direction,

Doug

 

[Fluff]

As you appear to have non-braking spaces in the gaps, another option is

Fluff.xlsm
	A	B
1
2	Positive seal sealing and holding is accomplished instantaneously Reusable connect and disconnect numerous times Full flow fitting seals on outside diameter of tubing Internal hex allows ease of assembly in tight places Pre-applied thread sealant	Positive seal sealing and holding is accomplished instantaneously</li><li>Reusable connect and disconnect numerous times</li><li>Full flow fitting seals on outside diameter of tubing</li><li>Internal hex allows ease of assembly in tight places</li><li>Pre-applied thread sealant
Data

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN("</li><li>",,TRIM(TEXTSPLIT(A2,CHAR(160))))

If you had posted your data using the XL2BB add-in that could have been seen as shown above.

 

[duggie33]

Wish I figured out those were there, would have saved me a bunch of time screwing around. I could have screwed around on other stuff. I am not working on anything without data anymore

Besides the point now, is there a way to do multiple REPLACEs in a single formula? I am sure a recursive LAMBDA or VBA could handle this without a problem...I am wondering about a worksheet formula that is less complicated than a recursive LAMBDA.

Doug

 

[Fluff]

You would need Lambda or VBA

 

[esb128]

=TEXTJOIN("</li><li>",,TRIM(TEXTSPLIT(A2,CHAR(160))))

thank you very much, it wont work i think there is a formatting issue in the data, im attaching an image of what the text looks like in the cell vs what it looks like in the formula box, in the cell there are no spaces but in the formula box it has spaces.

 

[duggie33]

You will either have to provide XL2BB data or learn how to determine what characters are in your text strings. My example above should help you determine what characters are in your strings.

Doug