Substitute function

Topic

New Reply

I have a cell which contains a mix of letters and numbers. I want to remove all the none numeric characters to give me the numbers.

eg

a 12 v would give 12
3412 fgd would give 3412
1234 would give 1234
sdf would give blank

I’ve thought about using substitution, but that would require lots of nested substitution functions to remove “a” then “b” then “c” etc

Any thoughts of how I can do this WITHOUT using VB?

John

Reply | Quote

Replies

Assuming that there will only be one (contiguous) number part in the string, you can do this with a series of formulas, including some array formulas (confirmed with Ctrl+Shift+Enter). See the attached sample workbook.

Reply | Quote

There are probably nicer formulas but this seems to work (array-entered):
=IF(ISNA(MATCH(TRUE,ISNUMBER(-MID(A1,ROW($1:$255),1)),0)),””,–MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,ROW($1:$255),1)),0),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},””)))))

Reply | Quote

Many thanks

I’ll adapt it to work with what I need

John

Reply | Quote