Find & Replace question

[Stuart]

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.

 

[Mike Fogleman]

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

Mike F

 

[Stuart]

Doesn't Replace supercede Substitute in XL2003?

Anyway, I couldn't get either to work, so I highlighted a
cell in the sheet, clicked Edit>Replace 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.

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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

 

[Stuart]

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.

 

[Tom Ogilvy]

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.

 

[Stuart]

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]

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.

 

[Stuart]

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]

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

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

 

[Stuart]

Many thanks for all your help.

Regards.

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

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