Hidden space on end of part number

[Dausa67]

Good morning,
PROBLEM
I am having trouble systematically removing a hidden character or space from part numbers.

DETAILS
1. Table is linked to an excel sheet where the hidden character problem starts.
2. Each excel sheet is different in size.
3. Not every part number has the hidden character on the end.

The only way to see the hidden character is to change the font to symbol.​

GOAL
The data list is huge so my goal is to find away to systematically remove the hidden character on the end of the affected part numbers. Doing it manually can take days.

EXAMPLE
Normal Arial font -----------------------------------System font
01APP0613-327 ------------------------------------------01APP0613-327
106150-0001 --------------------------------------------106150-0001
612UN01A01A5 -------------------------------------------612UN01A01A5

In the example above changed the font on the hidden character to System font.

So far I have tried to Trim in excel and in Access 2003. Neither one picks up that there is a character there.

QUESTION
Has anyone else had this problem?
Is there a method to systematically resolve this issue?

Thanks for any help.

Clay

 

[SkipVought]

Hi,

Use the Right() function and Asc() function to determine exactly what character is there.

 

[dhookom]

I would probably create a user-defined function in Excel that strips out any unwanted characters from the cell value.

Duane
Hook'D on Access
MS Access MVP

 

[Dausa67]

SKIP, I did what you suggested but it is still not visible to the eye (unless) I change the font.

Dhookom, I don't think that I have ever done a user-defined function in Excel. Is that something that you can easily explain to me?

Clay

 

[kray4660]

Of course if you want to remove it you can do a left(field, len(field) -1).

 

[dhookom]

kray4660,
I'm not sure how your answer would work if there are values that don't have an invisible character at the end.

Skip could create the function in half the time it would take me and my day job is pulling me away.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

I did not say it would be visible. The ASCII code NUMEIC value will possibly tell us how it got ther and how it might be avoided the future.

 

[Dausa67]

Kray, Not every part number has the hidden character on the end so that will not work.

Skip, unfortunately the files come from multiple sources in France. I have requested that they be aware of this but was introduced to brick walls.


Clay

 

[SkipVought]

Garbage in: Garbage out!

If you cannot control the data coming into your system, you WILL have problems!

 

[Dausa67]

I totally agree. Unfortunately it is engineers that are entering the data that don't care about the downstream users.

Clay

 

[SkipVought]

Take it to your management.

Either the data entry gets the appropriate edits or there may be a better source for this data or you're up the creek without a paddle.

 

[Andrzejek]

In the mid-time you may try something like this.
I've noticed your part numbers end with a digit, so you may try:

Dim str As String
str = "01APP0613-327?"
MsgBox ValidateMyString(str)

Public Function ValidateMyString(ByRef str As String) As String
str = Trim(str)
Select Case Right(str, 1)
    Case 0 To 9
        'All is fine, we are done
    Case Else
        str = Left(str, Len(str) - 1)
End Select
ValidateMyString = str
End Function

This function will strip the last charater of passed string if it is not a number/digit.
You may modify it more to make it more complex, like put a loop in the Function to deal with more than 1 character.

But I agree with Skip - ask for a clean data (if you can)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

Agreed on cleaning the data.
I would make the function a little more generic to remove any non-standard characters from anywhere in the string:

Public Function ValidateMyString(strIn As String) As String
    Dim strChar As String
    Dim strOut As String
    Dim intChar As Integer
    For intChar = 1 To Len(strIn)
        strChar = Mid(strIn, intChar, 1)
        Select Case Asc(UCase(strChar))
            [COLOR=#4E9A06]'hyphen, numbers 0 to 9, and letters A to Z[/color]
            Case 45, 48 To 57, 65 To 90
                strOut = strOut & strChar
            [COLOR=#4E9A06]'could add more Cases to allow additional characters[/color]
        End Select
    Next
    ValidateMyString = strOut
End Function

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

You should be able to use the Clean() function in excel. It removes non printable characters.