Clearing some character from one column only

Hi Jessica,

Thank you for the forum question.

You don’t need to format it manually—this can be done cleanly with a formula. Below are three solid options, depending on your Excel version.
________________________________________
Best solution (Excel 365 / Excel 2021 – no helper columns)
This removes all letters and leading zeros, leaving just the number:
=LET(
x, TEXTJOIN("",,IF(ISNUMBER(--MID(A1,SEQUENCE(LEN(A1)),1)),MID(A1,SEQUENCE(LEN(A1)),1),"")),
VALUE(x)
)
Examples
• AD005231 → 5231
• CH000231 → 231
• AD012345 → 12345
Works regardless of the letters used
Automatically removes leading zeros
Non-destructive (original data stays)
________________________________________
Simple & compatible (works in older Excel versions)
If the numbers always come at the end, use this classic formula:
=VALUE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1)))
No special Excel version required
Strips characters before the first number
Removes leading zeros
________________________________________
Power Query (best for large datasets)
If this is a repeatable task:
1. Select the column
2. Data → From Table/Range
3. In Power Query:
o Transform → Extract → Text After Delimiter (use a number if consistent)
o Or Transform → Format → Remove Non-Digits
4. Change type to Whole Number
5. Close & Load
Fast
Very reliable
Great for thousands of rows
________________________________________
Which one should you use?
Scenario Recommendation
Excel 365 / 2021 LET + TEXTJOIN formula
Older Excel MID / FIND formula
Large or recurring datasets Power Query



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector