help with spaces in funtions
 

I have in cell F1 open 4+ 60k
in F2 c6 50k
in F3 c4 522.5k I need the last part of each cell
without the "K'

eg: F1 open 4+ 60k = open(space)4+(space space)60k result 60
F2 c6 50k = c6(space space)50k
result 50
F3 c4 522.5k = c4(space)522.5k
result 522.5
The list goes down to 300 rows
I have tried some find functions but can not get the correct result for each
row

hope some one can help

Hi!

Try this:

=SUBSTITUTE(MID(F1,FIND("~",SUBSTITUTE(F1,"
","~",LEN(F1)-LEN(SUBSTITUTE(F1," ",""))))+1,255),"k","")*1

This is case sensitive based on the "k". If the "k's" in your cells are
actually in upper case then make the "k" in the formula also upper case.

OR use this slightly longer version where it doesn't matter:

=SUBSTITUTE(MID(F1,FIND("~",SUBSTITUTE(F1,"
","~",LEN(F1)-LEN(SUBSTITUTE(F1," ",""))))+1,255),RIGHT(F1,1),"")*1

Copy down as needed.

Biff

"bill gras" wrote in message
...
I have in cell F1 open 4+ 60k
in F2 c6 50k
in F3 c4 522.5k I need the last part of each cell
without the "K'

eg: F1 open 4+ 60k = open(space)4+(space space)60k result 60
F2 c6 50k = c6(space space)50k
result 50
F3 c4 522.5k = c4(space)522.5k
result 522.5
The list goes down to 300 rows
I have tried some find functions but can not get the correct result for
each
row

hope some one can help

Thank you for your swift reply
What would we do , without people like you
Thanks
bill

"Biff" wrote:

Hi!

Try this:

=SUBSTITUTE(MID(F1,FIND("~",SUBSTITUTE(F1,"
","~",LEN(F1)-LEN(SUBSTITUTE(F1," ",""))))+1,255),"k","")*1

This is case sensitive based on the "k". If the "k's" in your cells are
actually in upper case then make the "k" in the formula also upper case.

OR use this slightly longer version where it doesn't matter:

=SUBSTITUTE(MID(F1,FIND("~",SUBSTITUTE(F1,"
","~",LEN(F1)-LEN(SUBSTITUTE(F1," ",""))))+1,255),RIGHT(F1,1),"")*1

Copy down as needed.

Biff

"bill gras" wrote in message
...
I have in cell F1 open 4+ 60k
in F2 c6 50k
in F3 c4 522.5k I need the last part of each cell
without the "K'

eg: F1 open 4+ 60k = open(space)4+(space space)60k result 60
F2 c6 50k = c6(space space)50k
result 50
F3 c4 522.5k = c4(space)522.5k
result 522.5
The list goes down to 300 rows
I have tried some find functions but can not get the correct result for
each
row

hope some one can help

Assuming that k is the last digit in each cell...........
Delete the k first by using the function : =SUBSTITUTE(F1,"k",,1) in cell
G1 and extend down the column

To get writ of a column
You could use the substitute function once more to substitute "n " with "n"
so that open4+ becomes one word.

Change the result into values by edit, copy,edit, paste special, values.
into column H
Select all cells in H column
Use the data menu and select text to columns.
select delimited, next
space
treat consecutive delimiters as one, next
columns data format: general
destination: I1 (if available)
advanced: decimal separator: "."
finish

All the last cells are now the numbers you required.

--
Greetings from New Zealand
Bill K

"bill gras" wrote in message
...
I have in cell F1 open 4+ 60k
in F2 c6 50k
in F3 c4 522.5k I need the last part of each cell
without the "K'

eg: F1 open 4+ 60k = open(space)4+(space space)60k result 60
F2 c6 50k = c6(space space)50k
result 50
F3 c4 522.5k = c4(space)522.5k
result 522.5
The list goes down to 300 rows
I have tried some find functions but can not get the correct result for
each
row

hope some one can help

Hi Biff

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
--
bill gras


"Biff" wrote:

Hi!

Try this:

=SUBSTITUTE(MID(F1,FIND("~",SUBSTITUTE(F1,"
","~",LEN(F1)-LEN(SUBSTITUTE(F1," ",""))))+1,255),"k","")*1

This is case sensitive based on the "k". If the "k's" in your cells are
actually in upper case then make the "k" in the formula also upper case.

OR use this slightly longer version where it doesn't matter:

=SUBSTITUTE(MID(F1,FIND("~",SUBSTITUTE(F1,"
","~",LEN(F1)-LEN(SUBSTITUTE(F1," ",""))))+1,255),RIGHT(F1,1),"")*1

Copy down as needed.

Biff

"bill gras" wrote in message
...
I have in cell F1 open 4+ 60k
in F2 c6 50k
in F3 c4 522.5k I need the last part of each cell
without the "K'

eg: F1 open 4+ 60k = open(space)4+(space space)60k result 60
F2 c6 50k = c6(space space)50k
result 50
F3 c4 522.5k = c4(space)522.5k
result 522.5
The list goes down to 300 rows
I have tried some find functions but can not get the correct result for
each
row

hope some one can help