Find & Replace question
 

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.

Find & Replace question
 

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.

Find & Replace question
 

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.

Find & Replace question
 

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.

Find & Replace question
 

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.

Find & Replace question
 

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.

Find & Replace question
 

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.

Find & Replace question
 

What a pilchard(g)!

Right it's a "9" and this seems to work:

..Range("B22").Value = Replace _
(.Range("B22").Value, vbCr, "")
..Range("B22").Value = Replace _
(.Range("B22"), Chr(9), " ")

I'm using a blank string to replicate the effect of tab in the
userform textbox. I'll experiment to get the correct length.

I had to use Select with your code, and couldn't change it to duplicate the
results using Replace.

The array is pretty nifty, though.

Many thanks.

Regards.

"Tom Ogilvy" wrote in message
...
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.

Find & Replace question
 

Sub ReplaceCharacters()
v = Array(Chr(10), Chr(9))
v1 = Array(""," ")
For i = LBound(v) To UBound(v)
Range("B22").MergeArea.Replace What:=v(i), _
Replacement:=v1(i), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next
End Sub

perhaps.

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
What a pilchard(g)!

Right it's a "9" and this seems to work:

.Range("B22").Value = Replace _
(.Range("B22").Value, vbCr, "")
.Range("B22").Value = Replace _
(.Range("B22"), Chr(9), " ")

I'm using a blank string to replicate the effect of tab in the
userform textbox. I'll experiment to get the correct length.

I had to use Select with your code, and couldn't change it to duplicate
the
results using Replace.

The array is pretty nifty, though.

Many thanks.

Regards.

"Tom Ogilvy" wrote in message
...
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.

Find & Replace question
 

Many thanks.
That routine is an elegant way to deal with the many
text-based files that I have to import into Excel.

One last question, please:

I now know the values Chr(9) and Chr(10).
Is there somewhere I can see all the Chr numbers and
their associated worksheet values?

Regards.

"Tom Ogilvy" wrote in message
...
Sub ReplaceCharacters()
v = Array(Chr(10), Chr(9))
v1 = Array(""," ")
For i = LBound(v) To UBound(v)
Range("B22").MergeArea.Replace What:=v(i), _
Replacement:=v1(i), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next
End Sub

perhaps.

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
What a pilchard(g)!

Right it's a "9" and this seems to work:

.Range("B22").Value = Replace _
(.Range("B22").Value, vbCr, "")
.Range("B22").Value = Replace _
(.Range("B22"), Chr(9), " ")

I'm using a blank string to replicate the effect of tab in the
userform textbox. I'll experiment to get the correct length.

I had to use Select with your code, and couldn't change it to duplicate
the
results using Replace.

The array is pretty nifty, though.

Many thanks.

Regards.

"Tom Ogilvy" wrote in message
...
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.

Find & Replace question
 

http://www.deaneng.com/csi_wan/chart.html

or google search for an ascii chart. Anything below 32 are considered
non-printable characters.

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
Many thanks.
That routine is an elegant way to deal with the many
text-based files that I have to import into Excel.

One last question, please:

I now know the values Chr(9) and Chr(10).
Is there somewhere I can see all the Chr numbers and
their associated worksheet values?

Regards.

"Tom Ogilvy" wrote in message
...
Sub ReplaceCharacters()
v = Array(Chr(10), Chr(9))
v1 = Array(""," ")
For i = LBound(v) To UBound(v)
Range("B22").MergeArea.Replace What:=v(i), _
Replacement:=v1(i), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next
End Sub

perhaps.

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
What a pilchard(g)!

Right it's a "9" and this seems to work:

.Range("B22").Value = Replace _
(.Range("B22").Value, vbCr, "")
.Range("B22").Value = Replace _
(.Range("B22"), Chr(9), " ")

I'm using a blank string to replicate the effect of tab in the
userform textbox. I'll experiment to get the correct length.

I had to use Select with your code, and couldn't change it to duplicate
the
results using Replace.

The array is pretty nifty, though.

Many thanks.

Regards.

"Tom Ogilvy" wrote in message
...
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.

Find & Replace question
 

Many thanks for all your help.

Regards.

"Tom Ogilvy" wrote in message
...
http://www.deaneng.com/csi_wan/chart.html

or google search for an ascii chart. Anything below 32 are considered
non-printable characters.

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
Many thanks.
That routine is an elegant way to deal with the many
text-based files that I have to import into Excel.

One last question, please:

I now know the values Chr(9) and Chr(10).
Is there somewhere I can see all the Chr numbers and
their associated worksheet values?

Regards.

"Tom Ogilvy" wrote in message
...
Sub ReplaceCharacters()
v = Array(Chr(10), Chr(9))
v1 = Array(""," ")
For i = LBound(v) To UBound(v)
Range("B22").MergeArea.Replace What:=v(i), _
Replacement:=v1(i), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next
End Sub

perhaps.

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
What a pilchard(g)!

Right it's a "9" and this seems to work:

.Range("B22").Value = Replace _
(.Range("B22").Value, vbCr, "")
.Range("B22").Value = Replace _
(.Range("B22"), Chr(9), " ")

I'm using a blank string to replicate the effect of tab in the
userform textbox. I'll experiment to get the correct length.

I had to use Select with your code, and couldn't change it to
duplicate
the
results using Replace.

The array is pretty nifty, though.

Many thanks.

Regards.

"Tom Ogilvy" wrote in message
...
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.