Excel Remove numbers from text string

Question

Hi,

I would like to remove numbers form cells containing text ,

I thought of doing it with nested substitute, and even though I think this is going to work there must be a better way, suggestion please;

I do need this as a formula not using find replace, I can have the 'replace criteria' in this case 1 to 9 in a cell to reference if that helps?

Richard

Answer 1

=TEXTJOIN("",,LET(a,MID(E4,SEQUENCE(1,LEN(E4)),1),IF(ISNUMBER(--a),"",a)))

Answer 2

Hi,

In cell B2, enter this formula

=TEXTJOIN("",TRUE,TEXTSPLIT(A2,SEQUENCE(10,,0),,TRUE))

Hope this helps.

Answer 3

you have no delimter

No - the SEQUENCE part create the digits 0-9 and all of those are used as delimiters, so it splits wherever any digit appears, which effectively removes them all from the text before it is joined back together.

Answer 4

Just to say I had a go in PQ and came up with

Table.TransformColumns( Source,

       List.Transform( ColumnNames, (A)=> { A , (B)=>  Text.Remove(B, "list of text" )  } ))

I'm having a go with List accumulate as an alternative.

Richard.

Answer 5

Thanks, I've just tried TEXTSPLIT(F11,ROWS($H$11:H11)) ,

and the penny has dropped as it splits on each number produced,

Thanks, something else not mentioned or well hidden on ms docs.

Richard.