Share via

Formula to remove characters within a text string

Anonymous
2011-08-08T17:59:54+00:00

I have a column with text strings that that follow the format ABC000000012345

I need a formula to remove the 0000000 and return ABC12345

There are always three letters at the start, and always 7 zeros.

Suggestions appreciated

Microsoft 365 and Office | Excel | For home | 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
    2011-08-08T18:18:23+00:00

    Hi,

    assuming the text is in A1 enter

    =SUBSTITUTE(A1,0,"")

    4 people found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2011-08-08T18:12:09+00:00

    Hi,

    Assuming your string in A1, try this and drag down as required

    =LEFT(A1,3) & MID(A1,11,256)

    3 people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-08-09T14:59:11+00:00

    Hi Mike

    Maybe shorter : =REPLACE(A1,4,7,"")

    Regards

    0 comments
  2. Anonymous
    2011-08-09T14:58:12+00:00

    Hi,

    What if     ABC000000012305

    Your formula will remove the 0 after 123.

    Regards

    JY

    0 comments