Replace 2 or more spaces with text

[goble]

Hello,

I have :

Serial	Name	Composition	Data for HTML
1	Destadine Syrup	Each ml contains: Desratadine BP …….. 0.5 In a flavoured syrup base	FORMULA (Thanks to @Fluff) ="<tr><td>"&TEXTJOIN("</td><td>",,SUBSTITUTE(LEFT(A2:C2,LEN(A2:C2)-1),CHAR(10),"<br>"))&"</td></tr" Results = <tr><td>Destadine Syrup</td><td>Each ml contains:<br>Desratadine BP …….. 0.5 mg<br>In a flavoured syrup bas</td></tr
2	Terbinafine	Each uncoated tablet contains: Terbinafine Hydrochloride BP eq. to Terbinafine ......................... 250 mg

I would like data in C3 to look like C2 i.e. C3 should have it's data on each line instead of spaces (yes these are spaces since I can get rid of them using 'TRIM').

I was thinking of a formula that replaces 2 or more space with <br>. At the end of the day (whether data in column C are on different lines or separated by spaces) my final results should be like column D.

 

[kanadaaa]

Select D2 and move the cursor to the right bottom corner of the cell until you see a cross, then left-click and drag it down to D3.
If the text in C3 is separated into lines properly (now not) you should get the value you want in D3 with <br>, if this is what you mean.

 

[goble]

Hello and thanks,

Drag it down will make the same as CTRL-D in D3, no ? Anyway I get the same result as CTRL-D.

Also, I saw that the spaces went away after I posted the above.
Cell C3 should read :
"2 spaces here without quotes"Each uncoated tablet contains:"48 spaces here without quotes"Terbinafine Hydrochloride"36 spaces here without quotes"BP eq. to Terbinafine ......................... 250 mg"

 

[goble]

Hello,

am posting again to clarify the above (and since I haven't been able to sort this out).

I would like to change the cell from:
12345(4 spaces in between)abcd(12 spaces in between)xyz(5 spaces in between)789
to:
12345<br>abcd<br>xyz<br>789

So change 2 or more spaces in Cell with <br>

 

[FormR]

I would like to change the cell from:
12345(4 spaces in between)abcd(12 spaces in between)xyz(5 spaces in between)789
to:
12345<br>abcd<br>xyz<br>789

Hi - something like this maybe:

Book1
	A	B
1	12345 abcd xyz 789	12345<br>abcd<br>xyz<br>789
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(TRIM(A1)," ","<br>")

 

[goble]

Hi - something like this maybe:

Book1
	A	B
1	12345 abcd xyz 789	12345<br>abcd<br>xyz<br>789
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(TRIM(A1)," ","<br>")

beautiful, thanks !