I'll give it a try.. thanks!!!!

"Gary Keramidas" wrote:

why not just use this

If Not temp Like "*[!0-9]*" Then

instead of

If temp < " " And temp < "$" And Asc(temp) < WEIRD_SPACE Then


you only want the numbers, anyway.

(assist to rick)
--


Gary


"matt" wrote in message
...
This is the trim function I have and it seems to be working:

Private Const WEIRD_SPACE = 160
__________________________________________________ _

My other code stuff, where I call the myTrim Function
__________________________________________________ _
Function myTrim(s As String) As String
On Error Resume Next
Dim i As Integer
Dim s2 As String
Dim temp As String

For i = 1 To Len(s)
temp = Mid(s, i, 1)
If temp < " " And temp < "$" And Asc(temp) < WEIRD_SPACE Then
s2 = s2 & Mid(s, i, 1)
End If
Next

Err.Clear
myTrim = s2
End Function

"Rick Rothstein (MVP - VB)" wrote:

Left(CLng(Trim(Mid(Replace(YourValue, Chr(160), ""), 2))), 4)

Left(Trim(Mid(Replace(Replace(YourValue, ",", ""), Chr(160), ""), 2)), 4)

Rick


"matt" wrote in message
...
Thanks for the code Rick. But one of the Developers at my work gave me a
hand, guy is a whizzz. He had ended up having to write his own trim
function
in like a couple of minutes. And when we were steping through the code,
one
of the "spaces" was not really a space, we did a watch on each character
that
the trim was going to remove and one of them returned to us an Ascii value
of
160 which in the cell looks like a space but it was not recogniving it as
one.
I think this is because when I saved this file as an Excel file the
origional was not .xls. But all is well.
Thanks everyone.

"Rick Rothstein (MVP - VB)" wrote:

Whoops... I missed seeing the commas. Try one of these instead...

For constant 4 spaces
============================
Mid(CLng(YourValue), 6, 4)

or

Mid(Replace(YourValue, ",", ""), 6, 4)


For variable number of spaces
============================
Left(CLng(Trim(Mid(YourValue, 2))), 4)

or

Left(Trim(Mid(Replace(YourValue, ",", ""), 2)), 4)

Rick


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Are there **always** four spaces between the dollar sign and first
digit?
If so, this will return the first four digits of your value...

Mid(YourValue, 6, 4)

If the spaces can vary, then try this...

Left(Trim(Mid(YourValue, 2)), 4)

Rick



"matt" wrote in message
...
Thanks these all worked great!!!!
There is another problem now. There is another value that I find and
then
get the offset of one but the number value in the cell looks like this
including spaces:
i.e... "$ 11,234,500"
Is there a way to remove the "$ " and the four spaces after the
dollar
sign. Then I need the first four numbers.
I tried LTrim and some other things but found out LTrim only removes
spaces
in the very beginning.

Thanks,
Matt

"JLGWhiz" wrote:

I would not try to use the Copy method for this. Something like this
might
be more efficient.

Sub GetPartOfValue()
Dim myString As String, c As Range
Set c = Cells.Find(myString, LookIn:=xlValues)
If Not c Is Nothing Then
modVal = Left(c.Offset(0, 1).Value, 4)
Sheets("Destination").Range("newRange") = modVal
End If
Emd Sub

This finds the myString variable, assigns the first four characters
of
the
value in the cell to the right of myString to a variable, then
assigns
that
value to a newRange cell in the Destination sheet. You would
substitute
the
actual sheet name and Range address for "Destination" and "newRange".

"matt" wrote:

Also can I have it remove some numbers to the right, in the column
that is
next to the string.
So it finds the string im searching for say in ("B10"), then I want
it
to
trim the number in ("C10") so there is only four digits left and
then
do a
".Copy" of that cell.



"matt" wrote:

Hi,
I figuered out how to search through the sheet and find a string.
With
something like Cells.Find("My String").Activate
How do I have it do a .Copy of the cell to the right of that one.
So
if it
finds the string in "B10" then I want it to Copy "C10".
Thanks in advance....