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