remove a specified part of cell content (large data)

[Gizmo]

Wanted also ask for this one:

Is there any possibility to get rid of a specified part of cells?
I'll explain it on the following example:
There is a large amount of data, and all of them were put in general format,
but with some "dummy noise" put manually, i.e.

4.25%
5.50%
5.25%
etc.

Another example would be the range of cells in the following format:
4 Stk
5 Stk
8 Stk
etc...

How can I eliminate the "%" (or "Stk") sign, so I can treat the data as pure
numbers?
I've tried some special "custom" formating, but without much success...

Thanks in advance!

 

[Jason Morin]

Try:

=LEFT(A1,MAX(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN
(A1))),1)*1),ROW(INDIRECT("1:"&LEN(A1))))))*1

Array-entered, meaning after inserting the formula, press
ctrl/shift/enter. Now fill down. Change the reference A1
in the formula to suit your needs.

HTH
Jason
Atlanta, GA

 

[Don Guillett]

Without removing you could use this to add 3 to 4 stk to get 7 or 44 stk to
get 47
=LEFT(D15,FIND(" ",D15)-1)+3

 

[Gizmo]

Thanks Jason, it's working perfectly!
You are a genius...

 

[Aladin Akyurek]

=IF(ISNUMBER(A1),--SUBSTITUTE(A1,"%",""),--REPLACE(A1,SEARCH("
",A1),LEN(A1),""))