replacing carriage return with funky square

[MCantu]

I want to have a user copy and paste a block of text into a text box on my form, and then VBA would pick out the useful information , and place it in the various feilds in the record for the user. (so they won't have to read the text and pick it out manually)

The information generally is in the same order every time.

sometimes the text block varies where the carriage return is in the text line.

since there is always some variation in the line of text, I am using what is constant to find what I want to pull out.

For instance, the word "FONE" will always be there in the line of text. what I want will always be (for example) 5 characters away from "FONE".

Most of the time I don't what a problem with the text... BUT

I may sometimes have a carraiage return in the middle of FONE

Example :
F
ONE

Now, VBA won't recognize FONE" instr(1,[mytextbox],"FONE")


I used VBA to remove chr(13) but it replaces it with this square character which is acting as a carriage return.
result :
F[]ONE

(The brackets represent the box)

What is this square? If I knew it's chr() I would replace it too- but I can't figure out what it is.

example:


what is pasted:

This is my text example
of what is pasted into my
box before I run the chr(13) replace



After replace chr(13):


This is my text example[]of what is pasted into my[]box before I run the chr(13) replace

 

[Mile-O]

What is this square? If I knew it's chr() I would replace it too- but I can't figure out what it is.

You've already said it - it's Chr() is 13.

 

[ShiftyJaffa]

I suspect there maybe a line feed control character immediatly after the carriage return. The Line feed ascii value is 10 and is not normally visible if a carriage return preceeds it. If you have a function to remove chr$(13)'s from your string, make it remove all chr$(10)'s as well.

for your info, vbcr = chr$(13) and vbcrlf = chr$(13) & chr$(10) which is used more in printing.

If that doesnt find it, try this to list out each character in your string and its ascii value, once you have the square's ascii value you can remove it with your function.

dim Count
For Count = 1 to len(MyString)
debug print mid(MyString,count,1) & " = " & asc(mid(MyString,count,1)) & " / ";
Next Count

 

[MCantu]

I don't know....

I took code from the board here and modified it :
Dim tmpStr As String 'builds string where CR exists
Dim sChar As String 'holds character to add
Dim counter As Byte 'std counter

If InStr(1, [sorthotelinfo], Chr(13)) <> 0 Then 'only loop if a cr is in the field
For counter = 1 To Len([sorthotelinfo])
If Mid([sorthotelinfo], counter, 1) = Chr(13) Then 'is it a cr?
sChar = "" 'if so replace with nothing
Else
sChar = Mid([sorthotelinfo], counter, 1) 'use the character that's there
End If

tmpStr = tmpStr & sChar 'build it
Next counter 'next char
[sorthotelinfo] = tmpStr 'pass result
Else
[sorthotelinfo] = tmpStr 'pass result
End If




If it is chr(13), why would the result of :

'Start with with this before remove chr(13)

This is
an example


'Be this :

This is[]an example


'And NOT this :

This isan example


'Which is the desired result?

??

 

[MCantu]

whoops

sorry- quick reply there- the last was a reply to the first poster

 

[ShiftyJaffa]

In the code you just supplied assuiming a line feed is the problem, replace

If Mid([sorthotelinfo], counter, 1) = Chr(13) Then 'is it a cr?

with

If Mid([sorthotelinfo], counter, 1) = Chr(13) or Mid([sorthotelinfo], counter, 1) = Chr$(10) Then 'is it a cr?

Hope that helps

Paul

 

[MCantu]

Yes

yes it does!!!!
thanks!

Now I just to get all the spaces out..
I did it by adding trim() somewhere...I can't remember where I put it
....

 

[MCantu]

Found it

tmpStr = Trim(tmpStr)
tmpStr = tmpStr & sChar 'build it


That wiped out all the spaces too.

Thanks!!!!!