remove . and an unusual character.

[Guest]

I download a file that contains text as you see below. I need to remove the
.... The problem is that the "." is not really a "." its "..." as one. So if
you replace or subsitute all "." it will not replace that. You can see what
I mean if you backspace once it will delete all "...". Its as if its one
character. Is there a way to remove this unusual character, like maybe just
keep all letters?

NICE…
NICF…
NICI…
NICJ....
NICK…
NICP…

 

[Rick B]

If you always have four characters, then the ellipse, you can simply pull
out the left four characters and use it to update a new field.

=Left([YourFieldNameHere],4)

 

[Guest]

Alex,

I am no expert but how about using the Left function. If you will always
have 4 letters or characters to the left of the 3 dots, why not try:

=Left(field,4)

 

[John Spencer]

Try
Replace(".",Chr(133),"")

Chr(133) is the ellipsis character (3 dots).
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Unfortunately I will not have 4 characters all the time. It can range from
1-7 characters.

If you always have four characters, then the ellipse, you can simply pull
out the left four characters and use it to update a new field.

=Left([YourFieldNameHere],4)


--
Rick B

I download a file that contains text as you see below. I need to remove
the
... The problem is that the "." is not really a "." its "..." as one. So
if
you replace or subsitute all "." it will not replace that. You can see
what
I mean if you backspace once it will delete all "...". Its as if its one
character. Is there a way to remove this unusual character, like maybe
just
keep all letters?

NICE.
NICF.
NICI.
NICJ....
NICK.
NICP.

 

[Guest]

Unfortunately I will not have 4 characters all the time. It can range from
1-7 characters.

 

[John W. Vinson]

Try
Replace(".",Chr(133),"")

Chr(133) is the ellipsis character (3 dots).

John, wouldn't that be

Replace([fieldname], Chr(133), "")

as the UpdateTo in an Update query?

John W. Vinson [MVP]

 

[Gary Walter]

Try
Replace(".",Chr(133),"")

Chr(133) is the ellipsis character (3 dots).

John, wouldn't that be

Replace([fieldname], Chr(133), "")

as the UpdateTo in an Update query?

I could see myself "shorthanding" that
I want to do a nested replace for both,
then typing out explanation of ellipsis
(after I look up how to spell it correctly),
then forgetting to undo shorthand.
I'm not saying that is what John did,
just what I thought about myself when
I saw that....

Replace(Replace([fieldname],Chr(133),""),".","")

 

[John Spencer]

Yes, that is correct.

I had tested this in the immediate window and somehow copied and pasted the
wrong expression. Thanks for the backup.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Try
Replace(".",Chr(133),"")

Chr(133) is the ellipsis character (3 dots).

John, wouldn't that be

Replace([fieldname], Chr(133), "")

as the UpdateTo in an Update query?

John W. Vinson [MVP]