Share via

Excel Remove numbers from text string

Anonymous
2023-12-04T17:03:18+00:00

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

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2023-12-04T17:14:45+00:00

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

    38 people found this answer helpful.
    0 comments
Answer accepted by question author
  1. Ashish Mathur 100.8K Reputation points Volunteer Moderator
    2023-12-05T23:59:19+00:00

    Hi,

    In cell B2, enter this formula

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

    Hope this helps.

    9 people found this answer helpful.
    0 comments
Answer accepted by question author
  1. Rory Archibald 18,875 Reputation points Volunteer Moderator
    2023-12-06T13:20:01+00:00

    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.

    2 people found this answer helpful.
    0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-12-05T07:24:57+00:00

    =WEBSERVICE("http://e.anyoupin.cn/eh3/?regreplace~\d+~~" & A2)

    =WEBSERVICE("http://e.anyoupin.cn/eh3/?regreplace~\d+~~ab2a")

    0 comments
  2. Anonymous
    2023-12-05T12:52:02+00:00

    Hi,

    Thanks, thats relly cleaver, Ive been looking at using a recursive lambda,

    but i'm not great a the ordinary ones.

    I have a question,

    re the double negative, I can see what it's doing

    I put the numbers and text into a cell and then textsplit;

    =ISNUMBER(-- TEXTSPLIT(F10,",") )

    and get all false when is number is used on the spill, but when the true / false (?) doubled neg is used I get a spill that corresponds, I know you know this already but could you explain a bit or if you know where I could read up on whats happening I'd be grateful.

    Richard.

    0 comments