Thread: Stripping Letters Out of a Column

Right, ladies and gents, I have a bit of conundrum here.

I've got a spreadsheet that has a column that has letters THEN a number. I only need the number.

The issue is that I can't just do a replace all because there're about 1 million rows in this list and there're about 100,000 different letter sequences before the numbers. The letter sequences are all also different lengths.

Is there a way to strip all the letters from this column alone (I suppose I could transfer it to another spreadsheet and strip out all letters, so do with that as you will) without ruining the rest of the spreadsheet?

Thanks in advance,

Bob

Hi,

This array (CTRL, SHIFT & ENTER) formula returns just the numeric portion of the cell contents

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$99),1)),0),COUNT(1*MID(A1,ROW($1:$99),1)))

note that the maximum length of the contents of A1 should be 99 characters. If this is going to be longer, alter the ROW($1:$99) portions of the formula.

Hi

I've got a spreadsheet that has a column that has letters THEN a number. I only need the number.

And also you mentioned that you have too much data, I think a more simple formula will work well.

Pls try this in B1 per example.

=LOOKUP(9^9;1*RIGHT(TRIM(A1);COLUMN($2:$2)))

Hope to helps you.