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.

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

Mike F
"Stuart" <[email protected]> wrote in message
news:[email protected]...
> 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 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.

"Mike Fogleman" <[email protected]> wrote in message
news:[email protected]...
> Not Replace..
> =SUBSTITUTE(A1,"|","")
>
> Mike F
> "Stuart" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Mike Fogleman" <[email protected]> wrote in message
> news:[email protected]...
> > Not Replace..
> > =SUBSTITUTE(A1,"|","")
> >
> > Mike F
> > "Stuart" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Mike Fogleman" <[email protected]> wrote in message
> news:[email protected]...
> > Not Replace..
> > =SUBSTITUTE(A1,"|","")
> >
> > Mike F
> > "Stuart" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
news:%23FB%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>> "Mike Fogleman" <[email protected]> wrote in message
>> news:[email protected]...
>> > Not Replace..
>> > =SUBSTITUTE(A1,"|","")
>> >
>> > Mike F
>> > "Stuart" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%23FB%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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.
> >>
> >> "Mike Fogleman" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Not Replace..
> >> > =SUBSTITUTE(A1,"|","")
> >> >
> >> > Mike F
> >> > "Stuart" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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.
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%23FB%[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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.
>> >>
>> >> "Mike Fogleman" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Not Replace..
>> >> > =SUBSTITUTE(A1,"|","")
>> >> >
>> >> > Mike F
>> >> > "Stuart" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> 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.
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%23FB%[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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.
> >> >>
> >> >> "Mike Fogleman" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Not Replace..
> >> >> > =SUBSTITUTE(A1,"|","")
> >> >> >
> >> >> > Mike F
> >> >> > "Stuart" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> 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.
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:%23FB%[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> 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.
>> >> >>
>> >> >> "Mike Fogleman" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > Not Replace..
>> >> >> > =SUBSTITUTE(A1,"|","")
>> >> >> >
>> >> >> > Mike F
>> >> >> > "Stuart" <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> >> 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.
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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" <[email protected]> wrote in message
> >> >> news:%23FB%[email protected]...
> >> >> > 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" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> 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.
> >> >> >>
> >> >> >> "Mike Fogleman" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > Not Replace..
> >> >> >> > =SUBSTITUTE(A1,"|","")
> >> >> >> >
> >> >> >> > Mike F
> >> >> >> > "Stuart" <[email protected]> wrote in message
> >> >> >> > news:[email protected]...
> >> >> >> >> 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 for all your help.

Regards.

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> 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" <[email protected]> wrote in message
>> >> >> news:%23FB%[email protected]...
>> >> >> > 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" <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> >> 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.
>> >> >> >>
>> >> >> >> "Mike Fogleman" <[email protected]> wrote in message
>> >> >> >> news:[email protected]...
>> >> >> >> > Not Replace..
>> >> >> >> > =SUBSTITUTE(A1,"|","")
>> >> >> >> >
>> >> >> >> > Mike F
>> >> >> >> > "Stuart" <[email protected]> wrote in message
>> >> >> >> > news:[email protected]...
>> >> >> >> >> 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.
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>