I'm using Excel to query an AS400 library file. The query is returning a
combination of numerical and alpha-numerical values. When I use VLookup to
find specific data that was returned by the query, the VLookup does not
recognize the values as they "appear" to be. There is apparently an
additional character in the value that is not apparent. I've tried
searching the data for the infamous Alt-0160 character, but I'm not finding
that. Any ideas as to what's going on, or how to compensate for it?

D.S.

Don,

Can you provide an example of the data being returned by the query?

Donnie

"D.S." wrote in message
...
I'm using Excel to query an AS400 library file. The query is returning a
combination of numerical and alpha-numerical values. When I use VLookup
to
find specific data that was returned by the query, the VLookup does not
recognize the values as they "appear" to be. There is apparently an
additional character in the value that is not apparent. I've tried
searching the data for the infamous Alt-0160 character, but I'm not
finding
that. Any ideas as to what's going on, or how to compensate for it?

D.S.

Part No ITNBR
#VALUE! 1FRG3238
#VALUE! 1FRG3239
1202301 1202301
1202301 1202301



Column 'A' and 'B' from my worksheet.
Column 'A' is populated with this formula:
=+VALUE(B1 or whatever the relevant cell is)

I'm actually using the VLookup for values in column 'A' , because it does
not
recognize the values in column 'B', perhaps a non blank character is hiding
there. The AS400 query is returning the values in column 'B'. The formula
in column 'A' is providing me with a limited work-around, BUT, it has
trouble with the AlphaNumerical values.

D.S.

"Donnie Stone" wrote in message
...
Don,

Can you provide an example of the data being returned by the query?

Donnie

"D.S." wrote in message
...
I'm using Excel to query an AS400 library file. The query is returning
a
combination of numerical and alpha-numerical values. When I use VLookup
to
find specific data that was returned by the query, the VLookup does not
recognize the values as they "appear" to be. There is apparently an
additional character in the value that is not apparent. I've tried
searching the data for the infamous Alt-0160 character, but I'm not
finding
that. Any ideas as to what's going on, or how to compensate for it?

D.S.

Hi,
Try

Application.worksheetfunctions.Clean(Trim(QueryVal ue))

The Clean removes non printing characters and the Trim leading or trailing spaces.

regards
Paul

"D.S." wrote in message ...
I'm using Excel to query an AS400 library file. The query is returning a
combination of numerical and alpha-numerical values. When I use VLookup to
find specific data that was returned by the query, the VLookup does not
recognize the values as they "appear" to be. There is apparently an
additional character in the value that is not apparent. I've tried
searching the data for the infamous Alt-0160 character, but I'm not finding
that. Any ideas as to what's going on, or how to compensate for it?

D.S.

Great, that works for column 'A', is it possible to use this somehow to
clean column 'B' during the query process and just eliminate the need for
column 'A' ?

D.S.

"Paul Robinson" wrote in message
om...
Hi,
Try

Application.worksheetfunctions.Clean(Trim(QueryVal ue))

The Clean removes non printing characters and the Trim leading or trailing
spaces.

regards
Paul

"D.S." wrote in message
...
I'm using Excel to query an AS400 library file. The query is returning
a
combination of numerical and alpha-numerical values. When I use VLookup
to
find specific data that was returned by the query, the VLookup does not
recognize the values as they "appear" to be. There is apparently an
additional character in the value that is not apparent. I've tried
searching the data for the infamous Alt-0160 character, but I'm not
finding
that. Any ideas as to what's going on, or how to compensate for it?

D.S.

Don,

Try this solution. It was posted a while back.

Sub trythis()

Dim s As String
Dim c As Range, myRng As Range

Set myRng = ActiveSheet.UsedRange.Columns(2).Cells

For Each c In myRng
With c
s = CStr(.Value)
s = UCase(WorksheetFunction.Clean(WorksheetFunction.Tr im(s)))
.Value = s
.Rows.AutoFit
End With
Next

End Sub
"D.S." wrote in message
...
Great, that works for column 'A', is it possible to use this somehow to
clean column 'B' during the query process and just eliminate the need for
column 'A' ?

D.S.

"Paul Robinson" wrote in message
om...
Hi,
Try

Application.worksheetfunctions.Clean(Trim(QueryVal ue))

The Clean removes non printing characters and the Trim leading or
trailing
spaces.

regards
Paul

"D.S." wrote in message
...
I'm using Excel to query an AS400 library file. The query is
returning
a
combination of numerical and alpha-numerical values. When I use
VLookup
to
find specific data that was returned by the query, the VLookup does
not
recognize the values as they "appear" to be. There is apparently an
additional character in the value that is not apparent. I've tried
searching the data for the infamous Alt-0160 character, but I'm not
finding
that. Any ideas as to what's going on, or how to compensate for it?

D.S.

I've been given a new job assignment, so I'll have to pull away from this
for a few days, will try soon, and post another reply later.

I will add though, that while I thought the < worksheetfunction.clean was
working, apparently it is not. The Cells in column 'A' (refer to earlier
post on 11/30/03), while appearing to display the correct value, are still
not recognized by the VLookup function.

Gratefully,
D.S.

"Donnie Stone" wrote in message
...
Don,

Try this solution. It was posted a while back.

Sub trythis()

Dim s As String
Dim c As Range, myRng As Range

Set myRng = ActiveSheet.UsedRange.Columns(2).Cells

For Each c In myRng
With c
s = CStr(.Value)
s = UCase(WorksheetFunction.Clean(WorksheetFunction.Tr im(s)))
.Value = s
.Rows.AutoFit
End With
Next

End Sub
"D.S." wrote in message
...
Great, that works for column 'A', is it possible to use this somehow to
clean column 'B' during the query process and just eliminate the need
for
column 'A' ?

D.S.

"Paul Robinson" wrote in message
om...
Hi,
Try

Application.worksheetfunctions.Clean(Trim(QueryVal ue))

The Clean removes non printing characters and the Trim leading or
trailing
spaces.

regards
Paul

"D.S." wrote in message
...
I'm using Excel to query an AS400 library file. The query is
returning
a
combination of numerical and alpha-numerical values. When I use
VLookup
to
find specific data that was returned by the query, the VLookup does
not
recognize the values as they "appear" to be. There is apparently an
additional character in the value that is not apparent. I've tried
searching the data for the infamous Alt-0160 character, but I'm not
finding
that. Any ideas as to what's going on, or how to compensate for it?

D.S.