Using XL2003:
I'm copying the contents of a multiline textbox from a
userform to a range on a sheet.
After the copy, the data on the sheet looks like this:

abc

|abc

||||abc etc

It seems "|" is the result of using the tab key in the textbox
Can I remove this from the sheet with Replace, please?

Regards.

Not Replace..
=SUBSTITUTE(A1,"|","")

Mike F
"Stuart" wrote in message
...
Using XL2003:
I'm copying the contents of a multiline textbox from a
userform to a range on a sheet.
After the copy, the data on the sheet looks like this:

abc

|abc

||||abc etc

It seems "|" is the result of using the tab key in the textbox
Can I remove this from the sheet with Replace, please?

Regards.

Doesn't Replace supercede Substitute in XL2003?

Anyway, I couldn't get either to work, so I highlighted a
cell in the sheet, clicked EditReplace and typed "|".
Excel reported nothing to replace.
I highlighted the range in question, and the formula bar showed loads of
'little square boxes' in the same places that "|" is visible in the sheet.
I printed out the sheet, and Excel printed the boxes.

How do I get rid of them please?

Regards.

"Mike Fogleman" wrote in message
...
Not Replace..
=SUBSTITUTE(A1,"|","")

Mike F
"Stuart" wrote in message
...
Using XL2003:
I'm copying the contents of a multiline textbox from a
userform to a range on a sheet.
After the copy, the data on the sheet looks like this:

abc

|abc

||||abc etc

It seems "|" is the result of using the tab key in the textbox
Can I remove this from the sheet with Replace, please?

Regards.

It doesn't look like a tab to me, tab shows as blank.

Try this though.

In an adjacent cell, assuming the data is in A1, input =CODE(LEFT(A1,1))
Whatever answer you get, use =SUBSTITUTE(A1,CHAR(x),"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stuart" wrote in message
...
Doesn't Replace supercede Substitute in XL2003?

Anyway, I couldn't get either to work, so I highlighted a
cell in the sheet, clicked EditReplace and typed "|".
Excel reported nothing to replace.
I highlighted the range in question, and the formula bar showed loads of
'little square boxes' in the same places that "|" is visible in the sheet.
I printed out the sheet, and Excel printed the boxes.

How do I get rid of them please?

Regards.

"Mike Fogleman" wrote in message
...
Not Replace..
=SUBSTITUTE(A1,"|","")

Mike F
"Stuart" wrote in message
...
Using XL2003:
I'm copying the contents of a multiline textbox from a
userform to a range on a sheet.
After the copy, the data on the sheet looks like this:

abc

|abc

||||abc etc

It seems "|" is the result of using the tab key in the textbox
Can I remove this from the sheet with Replace, please?

Regards.

Many thanks to you both.
Used Bob's answer to get character 113, but neither Bob's nor Tom's
suggestion got rid of it.

I used a test sheet from the userform, where I deliberately used the tab key
several times.

Nb: the paste from the form is going into a range of merged cells on the
sheet ("B22:K52"), with Wraptext
enabled. Would this be part of the problem?

Regards.

"Bob Phillips" wrote in message
...
It doesn't look like a tab to me, tab shows as blank.

Try this though.

In an adjacent cell, assuming the data is in A1, input =CODE(LEFT(A1,1))
Whatever answer you get, use =SUBSTITUTE(A1,CHAR(x),"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stuart" wrote in message
...
Doesn't Replace supercede Substitute in XL2003?

Anyway, I couldn't get either to work, so I highlighted a
cell in the sheet, clicked EditReplace and typed "|".
Excel reported nothing to replace.
I highlighted the range in question, and the formula bar showed loads of
'little square boxes' in the same places that "|" is visible in the
sheet.
I printed out the sheet, and Excel printed the boxes.

How do I get rid of them please?

Regards.

"Mike Fogleman" wrote in message
...
Not Replace..
=SUBSTITUTE(A1,"|","")

Mike F
"Stuart" wrote in message
...
Using XL2003:
I'm copying the contents of a multiline textbox from a
userform to a range on a sheet.
After the copy, the data on the sheet looks like this:

abc

|abc

||||abc etc

It seems "|" is the result of using the tab key in the textbox
Can I remove this from the sheet with Replace, please?

Regards.

113 is a lower case q

Assuming you literally used Bob's formula, this gives you ascii code of the
leftmost character in the string - which was probably a q.

what you would do would be

=code(Mid($B$22,row(),1))&"-"&Mid($B$22,row(),1)

then drag down the column to see what the ascii codes are for the string in
A1.


--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
Many thanks to you both.
Used Bob's answer to get character 113, but neither Bob's nor Tom's
suggestion got rid of it.

I used a test sheet from the userform, where I deliberately used the tab
key
several times.

Nb: the paste from the form is going into a range of merged cells on the
sheet ("B22:K52"), with Wraptext
enabled. Would this be part of the problem?

Regards.

"Bob Phillips" wrote in message
...
It doesn't look like a tab to me, tab shows as blank.

Try this though.

In an adjacent cell, assuming the data is in A1, input =CODE(LEFT(A1,1))
Whatever answer you get, use =SUBSTITUTE(A1,CHAR(x),"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stuart" wrote in message
...
Doesn't Replace supercede Substitute in XL2003?

Anyway, I couldn't get either to work, so I highlighted a
cell in the sheet, clicked EditReplace and typed "|".
Excel reported nothing to replace.
I highlighted the range in question, and the formula bar showed loads
of
'little square boxes' in the same places that "|" is visible in the
sheet.
I printed out the sheet, and Excel printed the boxes.

How do I get rid of them please?

Regards.

"Mike Fogleman" wrote in message
...
Not Replace..
=SUBSTITUTE(A1,"|","")

Mike F
"Stuart" wrote in message
...
Using XL2003:
I'm copying the contents of a multiline textbox from a
userform to a range on a sheet.
After the copy, the data on the sheet looks like this:

abc

|abc

||||abc etc

It seems "|" is the result of using the tab key in the textbox
Can I remove this from the sheet with Replace, please?

Regards.

select the bad cells

Sub ReplaceCharacters()
v = Array(Chr(10), Chr(13), Chr(27))
For i = LBound(v) To UBound(v)
Selection.Replace What:=v(i), _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next
End Sub

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
Doesn't Replace supercede Substitute in XL2003?

Anyway, I couldn't get either to work, so I highlighted a
cell in the sheet, clicked EditReplace and typed "|".
Excel reported nothing to replace.
I highlighted the range in question, and the formula bar showed loads of
'little square boxes' in the same places that "|" is visible in the sheet.
I printed out the sheet, and Excel printed the boxes.

How do I get rid of them please?

Regards.

"Mike Fogleman" wrote in message
...
Not Replace..
=SUBSTITUTE(A1,"|","")

Mike F
"Stuart" wrote in message
...
Using XL2003:
I'm copying the contents of a multiline textbox from a
userform to a range on a sheet.
After the copy, the data on the sheet looks like this:

abc

|abc

||||abc etc

It seems "|" is the result of using the tab key in the textbox
Can I remove this from the sheet with Replace, please?

Regards.