How to Remove specific characters from all lines in a cell

[IamSaleem]

Good Evening every one,

I've small problem in following case, your help is appreciated.

In a Cell i've following data

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]1. One
2. Two
3. Three[/TD]
[/TR]
</tbody>[/TABLE]

Now i want remove first 3 characters from each line and remove linebreaks and make as comma separated values.

O/p : One,Two,Three

Thanks

 

[Scott Huish]

Hi, welcome to the board!

Here is a UDF that will do that:

Function StringToCSV(r As String) As String
Dim s, t As Long
s = Split(r, Chr(10))
For t = 0 To UBound(s)
    s(t) = Mid(s(t), 4)
Next t
StringToCSV = Join(s, ",")
End Function

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:121px;" /><col style="width:227px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:55px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >1. One<br />2. Two<br />3. Three</td><td >One,Two,Three</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=StringToCSV(A1)</td></tr></table></td></tr></table>

 

[Rick Rothstein]

Good Evening every one,

I've small problem in following case, your help is appreciated.

In a Cell i've following data

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]1. One
2. Two
3. Three[/TD]
[/TR]
</tbody>[/TABLE]

Now i want remove first 3 characters from each line and remove linebreaks and make as comma separated values.

O/p : One,Two,Three

I am assuming you have more than one cell with similar dat and that you want to make this change to your original data (if not, copy your data to another column and use this procedure on that copy instead). Select the column or columns with the data you want to modify, then press CTRL+H to bring up the "Replace" dialog box. Next, click into the "Find what" field (remove any text that may be in it from a previous search) and type an asterisk followed by a dot followed by a space ("*. ") into, then move the text cursor to the beginning of the line (in front of the asterisk) and press CTRL+J... it will look like the field has been erased, but it hasn't been... CTRL+J is a shortcut for the Line Feed character that the dialog box recognizes, so what has happened is the asterisk/dot/space characters where moved to the next line which the "Find what" field cannot display. Okay, now type a comma into the "Replace with" field. Next, click the "Options" button and make sure there is no check mark in the "Match entire cell contents" checkbox. Finish this part of the process by clicking the "Replace All" button. Next, with the "Replace" dialog box still up, put the text cursor at the beginning of the "Find what" field and press the "Delete" key on your keyboard once which will leave the asterisk/dot/space in the field, next delete the contents of the "Replace with" field and click the "Replace" all button. Your text should now be displayed as you wanted.

 

[István Hirsch]

I am assuming you have more than one cell with similar dat and that you want to make this change to your original data (if not, copy your data to another column and use this procedure on that copy instead). Select the column or columns with the data you want to modify, then press CTRL+H to bring up the "Replace" dialog box. Next, click into the "Find what" field (remove any text that may be in it from a previous search) and type an asterisk followed by a dot followed by a space ("*. ") into, then move the text cursor to the beginning of the line (in front of the asterisk) and press CTRL+J... it will look like the field has been erased, but it hasn't been... CTRL+J is a shortcut for the Line Feed character that the dialog box recognizes, so what has happened is the asterisk/dot/space characters where moved to the next line which the "Find what" field cannot display. Okay, now type a comma into the "Replace with" field. Next, click the "Options" button and make sure there is no check mark in the "Match entire cell contents" checkbox. Finish this part of the process by clicking the "Replace All" button. Next, with the "Replace" dialog box still up, put the text cursor at the beginning of the "Find what" field and press the "Delete" key on your keyboard once which will leave the asterisk/dot/space in the field, next delete the contents of the "Replace with" field and click the "Replace" all button. Your text should now be displayed as you wanted.

Good idea, but it needs a third step because 3. is not followed by a space.

 

[Rick Rothstein]

Good idea, but it needs a third step because 3. is not followed by a space.

I think it is followed by a space, not a Line Feed... I think what looks like a Line Feed is in fact a word wrap introduced by whatever process the OP used to copy/paste his info into his message.

 

[István Hirsch]

I think it is followed by a space, not a Line Feed... I think what looks like a Line Feed is in fact a word wrap introduced by whatever process the OP used to copy/paste his info into his message.

I think you are right, and only copying produced this strange layout.