If anyone is following this thread Bill was a victim of the dreaded
CHAR(160)!

Suggested he use the Find/Replace commands and also refered him to David
McRitchie's TRIMALL macro.

Biff

"Biff" wrote in message
...
OK, sent you an email.

Biff

"bill gras" wrote in message
...

--
bill gras


"bill gras" wrote:

Hi Biff

You can contact me by email

thank you Bill Gras

p s Must be something with the import because it does not work properly.
When I typed in the same as you did without the import and it works
fine.
--
bill gras


"Biff" wrote:

Hi Bill!

Take a look at this screencap:

http://img210.imageshack.us/img210/8...numbers1le.jpg

As you can see, every entry that ends with numberK is extracted. The
cells
where you see #VALUE! correspond to your entry of "rubbish text".
Since
those cells don't contain a number to extract the formula will return
an
error. In the next column over is the same formula but I just hide the
#VALUE! errors.

So, the formula does work. I'm wondering if the problem your having
might be
caused by the importing. There may be unseen characters in the string
that
look like standard spaces but might be some other type of blank
character.
The formula is explicitly looking for CHAR(32) standard spaces.

Can you send me a copy of this file? I don't need the whole thing.
Maybe
just part of the sheet that contains this particular data. If you want
to do
that let me know how I can contact you. My email address is bogus so
don't
try using it. You'll just get bounced.

Biff

"bill gras" wrote in message
...
Hi Biff

I import a file to excel which contains data like this:
F1 OPEN 4+ 60K
F2 "rubish text" (crap I dont need)
F3 OPEN 4+ 50k
F4 "rubish text"
F5 "rubish text"
F6 C6 50K
F7 "rubish text"
F8 C4 4+ 22.7K
F9 "rubish text"
F10 C4 22.5K
F11 "rubish text"
F12 "rubish text"
F13 "rubish text"
F14 LY1 25K
F15 "rubish text"
Down to 300 rows all entries are in rows the rows are at random
and all entries are all in capital letters
Your formula gives me
F1 60
F2 #VALUE!
F3 #VALUE!
F4 #VALUE!
F5 #VALUE!
F6 50
F7 #VALUE!
F8 #VALUE!
F9 #VALUE!
F10 22.5
F11 #VALUE!
F12 #VALUE!
F13 #VALUE!
F14 #VALUE!
F15 #VALUE!
I also need to multiply the result by other numbers
I just realised that I did not give you the rows with
rubish text Sorry Biff my stupid mistake

Hope you can still help me

regards Bill Gras
--
bill gras


"bill gras" wrote:

The formula you gave me =substitute(mid(f1,find("~",substitute(f1,"
","~",len(f1)-len(substitute(f1," ",""))))+1,255),right(f1,1),"")*1
works
partley,it will only give me the first 3 rows with a result and the
other
297
rows have a #value! result
could you please help me I have tried to change parts of the
formula but
no
luck

thank you
--
bill gras