Remove All Text,Special Characters, & Spaces

Excel 2003: I have a column that looks that has values in it like this:

Please Login or Register  to view this content.

I need to reduce the column to just plain numbers no % no text, I have a program that does this but it is thrid party add-in and can't be used in a macro. I tried find and replace but that does not help as there are spaces and the spaces are not the same or in the same place. There is also for some reason special characters the / pops up in 5 different cells. I am trying to make this column so that I can sort it.

Thank you
Ken

Here, try this:

=LOOKUP(99^99, --RIGHT(SUBSTITUTE(A1, "%", ""), ROW($A$1:$A$10)))

assuming:
- the data in column A
- the numbers are being preceded by a space

Please Login or Register  to view this content.

Leave last part as it is and with $ around it ... Change only A1 into D30:


=LOOKUP(99^99, --RIGHT(SUBSTITUTE(D30, "%", ""), ROW($A$1:$A$10)))

I fear you didn't read my post correctly.
2 or more spaces do not matter. The only thing that matters is a space before the number.

or

PHP Code: 

=LOOKUP(99^99; --100*RIGHT(D30; ROW(1:10))) 


Here is explanation:

In Excel, the Lookup function returns a value from a range (one row or one column) or from an array.

If you put very big number it will return last value
(for example: =LOOKUP(9, {8,2,4,3,6,5}) will return number 5)
But if you have number 11 in your array you will get wrong result.
So you need to put very, very big number (such as 99^99 (which is 3,69729637649726E+197 so hardly you will have bigger number than that). If it's not enough you can use 9.99999999999999E+307).

So, =LOOKUP(9.99999999999999E+307, {8,2,4,3,6,5}) but now you are sure that there can not be any higher number then your criteria so it will always return last number from an array).

Now, when you know this you can split your word Black Cartridge 27% from the right (lats say it's in D30 as snb showed).

--100*RIGHT(D30; ROW(1:10)))

ROW(1:10) will give you number 1,2,3,4,5...,9,10

if you can have higher number then 10 digits (i.e. 14) you can put ROW(1:14) or any higher just to be sure ROW(1:100)

Now, it will take right part of your string with: 1 character, then 2, then 3 etc... up to 10 (or 14 or 100)

RIGHT("Black Cartridge 27%", ROW(1:10)))

Will give you an array: "%", "7%", "27%", " 27%" (there is space), "e 27%", "ge 27%", "dge 27%", "idge 27%", "ridge 27%", "artridge 27%"

-- or 100* (no need for both here but anyaway you need to convert those strings into numbers)

So converting it by -- or by 100* you will have new array:

100*"%", 100*"7%", 100*"27%", 100*" 27%", 100*"e 27%", 100*"ge 27%", etc

Multiplying you'll get result:

#VALUE!, 7, 27, 27, #VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!

And LOOKUP from the begging of explanation
=LOOKUP(9.99999999999999E+307, {#VALUE!, 7, 27, 27, #VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!})
will return last value that is not an error and that's 27 which si last number in your string.

Notice that if you change % with any other character (i.e. Black Cartridge 27# ) you will get error because right character would be text and you can never convert it to number.

Then you need to take my approach and substitute last characters while snb elegantly solved it by multiplying with 100.

If they are all percentages, you could also use:
=-LOOKUP(1,-RIGHT(D30,{1,2,3,4,5,6,7,8,9}))*100

For the VBA-solution see the attachment.