remove a specified part of cell content (large data)

  • Thread starter Thread starter Gizmo
  • Start date Start date
G

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!
 
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
 
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
 
=IF(ISNUMBER(A1),--SUBSTITUTE(A1,"%",""),--REPLACE(A1,SEARCH("
",A1),LEN(A1),""))
 
Back
Top