Formula to remove 0's from part of a text string

Do you have all the text ('TS01 to TS20') in Cell A1 on in A1 to A20??

Or to express it in other words:
In one cell you have
TS01
in other
TS03
yet in another TS20

or ...
in one cell TS01 to TS20
in other cell
TS05 to TS15
etc.

Next question: is text part always 2 characters (TS, KA PE ER etc. or could be different length.

It's very important especially if we plan to do this with as simple as possible formulas.

To use full power of this forum - will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.



PS. from the answer posted while I was writiong above it seems that we are in case 1 - just single value

But the "auto"solution does not care of such valies as
TS101 - may be not your case, but you not disclosed that number part will be max 2 digit long

Do the following
Select the Range
Ctrl+H >
What what = TS0 >
Replace with = TS >
replace all >
Close

b2=SUBSTITUTE(A2,LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&1/17))-1)&0,LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&1/17))-1))

Please Login or Register  to view this content.

Try this and copy towards down

My concept (if we stick to formulas):
B2 and copy down:

Formula:

Please Login or Register  to view this content.