How can remove " after do replace in beginning and end of cell

[parscon]

Hello I have data in A1 like :

1|2|3|4|5|6

and i replaced | with line break



Sub Replace()
Cells.Replace _
What:="|", Replacement:="" & Chr(10) & "", _
LookAt:=xlPart, MatchCase:=False
End Sub


and the result in A1 will be :
"1
2
3
4
5
6"

it does not show the quotation marks " in excel but when copy to text it will be appear . How can i remove them ? the result that i need is
1
2
3
4
5
6
Please help me on this subject because i need to run another vba and as the quotation marks it does not work well .

[snb]

I fear you don't even understand this simple line of VBA code.

[parscon]

I do not ask you to check my knowledge !if you can help write your answer if you cannot no need spam the post .

[MINCUS1308]

How peculiar.

I tried removing the beginning and end of the string but that just took the 1 & 6 and left the quotes.

what is that you are trying to do? perhaps there is another method?

[MINCUS1308]

If you removed the Chr(10) and replace it with a nothing ("") It will paste without the quotes. but it wont be separated onto a new line.

Sub test()
MyStr = Cells(1, 1).Value
Cells(2, 1).Value = Replace(MyStr, "|", "")
End Sub

you could then later break the string of characters apart and complete your macro

[MINCUS1308]

snb,

I see that the
chr(10) splits the line but why is it when you try to copy & paste the contents of the cell as text the quotes appear?

is there a
.NumberFormat that would allow me to see the quotations?

[MINCUS1308]

IF YOU HAVE SPACE TO SPARE IN YOUR WORKBOOK YOU COULD TRY THIS


Sub test()
'CAPTURE A1 VALUE
MyStr = Cells(1, 1).Value
'REMOVE '|' AND BUILD AN ARRAY OUT OF A1 VALUE
Dim MyArray As Variant
MyArray = Split(MyStr, "|")
'SPLIT THE ARRAY INTO VERTICAL & ADJACENT CELLS
For I = LBound(MyArray) To UBound(MyArray)
Cells(2, 1).Offset(I, 0).Value = MyArray(I)
Next I
'SELECT THE RANGE FOR COPY
Range("A2:A" & I + 1).Copy
'NOW YOU CAN PASTE WITHOUT THE QUOTES
End Sub

[Paul_Hossler]

I think the issue is that with the ASCII 10's embedded in the string, it has to be quoted and therefore looks like a String when it gets pasted

The Chr(34)'s are really not in the value, but do show as bracketing quotes when I paste into Notepad

21239

If it really is a problem, then the downstream macro would have to handle them

[p45cal]

two cents:
Sub blah()
Set myData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
myData.SetText Application.WorksheetFunction.Substitute(Selection.Value, "|", Chr(10))
myData.PutInClipboard
End Sub