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-05T13:00:51+00:00

    Double negative will force text to a number if it is "text format" number.

    0 comments
  2. Anonymous
    2023-12-06T13:05:09+00:00

    Thanks,
    that;s a pretty amazing method, have to say I understand Snow Lu's a bit better,

    So what's happening? you have no delimter yes I can see the number values are ignored,

    and that the fact that Sequence delivers a range of values makes a difference

    So here it's grouped the text values and spilled out the extra commas, it's a bit confusing

    as to how it works,. Richard.

    1 person found this answer helpful.
    0 comments