Remove specific digits in a number using a basic formula.

Question

Greetings I have a 8 digit characters that can begin with 0. I would like to remove the first, third, and fourth character. For example 97000048 would become 70048,

88000266 would be 80266.

Thank you so much,

Answer 1

Dear Embry,

I'm Ibhadighi and i'd happily help you with your question. In this fdorum, we are Microsoft consumers just like yourself.

You can use a formula that combines the LEFT, MID, and RIGHT functions to remove the first, third, and fourth characters from an 8-digit number. Here’s an example formula that you can use:

=LEFT(A1,1)&MID(A1,5,1)&RIGHT(A1,3)

This formula assumes that the 8-digit number is in cell A1. It extracts the first character using the LEFT function, the fifth character using the MID function, and the last three characters using the RIGHT function.

The & operator concatenates these characters to form the final result.

For example, if cell A1 contains the number 97000048, the formula will return 70048. If cell A1 contains the number 88000266, the formula will return 80266.

You can adjust the cell reference in the formula to fit your data.

Please let me know if this helps or if you have any further questions.

Best Regards, IBHADIGHI

Give back to the community. Help the next person with this problem by inicating whether this answer solved your problem.d Click Yes or No at the bottom.

Answer 2

Hi,

In cell B2, enter this formula

=LET(seq,SEQUENCE(LEN(A2)),TEXTJOIN("",TRUE,INDEX(DROP(MID(A2,SEQUENCE(10,,0),1),1),FILTER(seq,ISERROR(MATCH(seq,{1,3,4},0))),1)))

hope this helps.