Removing select informaion from cells

S

Sue

I have columns with numbers and symbols in. They look like this:

456456*
521369*
789456!

I have highlighted the column and i want to remove any numbers in the cells
but keep the symbol in the cell.
I tried shift and F but i couldnt get it to do this for any number that
appears in the cell, i found i had to type in the specific no then it would
work.
This is too time consuming, I have alot of columns!

Any help will be appriciated.

Thanks,
 
S

Sue

Thanks, that worked but some cells only had a number and i wanted it to be
left blank but it has remained the last number that was in the cell, eg:

456896 now left with 6

Any ideas?
 
G

Gary''s Student

=IF(ISNUMBER(-RIGHT(A1,1)),"",RIGHT(A1,1))
This converts a trailing digit into a blank.

Is this answer good enough??
 
S

Sue

Thanks, it worked!
In the same column i also had some numbers with no symbol. I wanted the
number removed leaving a blank cell. Instead it has left the last number in
the row eg:

456236 left 6

How can i change this?

Thanks
 
R

Ron Rosenfeld

I have columns with numbers and symbols in. They look like this:

456456*
521369*
789456!

I have highlighted the column and i want to remove any numbers in the cells
but keep the symbol in the cell.
I tried shift and F but i couldnt get it to do this for any number that
appears in the cell, i found i had to type in the specific no then it would
work.
This is too time consuming, I have alot of columns!

Any help will be appriciated.

Thanks,
Click to expand...

If all of your entries have at least 1 number, then:

=SUBSTITUTE(A1,LOOKUP(9.9+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))),"")

will return only the non-numbers.

If the cell is blank, it will return a #REF! error, and if the cell contains
only non-numbers, it will return a #N/A error.

You can add logic to test for that, if necessary.
--ron
 
S

Sue

Didnt mean to type question in twice!
I typed that formula into a blank cell but it wouldnt work, came up with a
box saying that the formulation contained and error, i copied it exactly.

Thanks
 
Top