How to remove special characters from a cell

[hsandeep]

In coulumn C, the value is a text which contains Starting with 2 digit alphabets, separated by ONE special character "/" or "-", then 4 to 8 digit numericals then again separated by ONE special character "/" or "-", then 2 to 3 digit numericals.

Output required in column D = All text without special characters
and in column E = last numerical value (of 2 to 3 digit numericals)
and in column F = above last numerical value prefixed with a 0.
Note: blanks are not to considered
Ex:
cell C4=MH / 456789 - 187
cell D4=MH456789187
cell E4=0187

cell C4=YH / 1237 - 000
cell D4=MH1237000
cell E4=0000

 

[hsandeep]

How to get second part without prefixing 0?
There are some values in the columns starting with DE:DH. Answers will be in DI, DJ & DK.

 

[hsandeep]

Sorry, I completely misread what you wanted for that first part. Sal Paradise gave you a working formula for the first part.

First Part: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D4,"/",""),"-","")," ","")

Last Part: =0&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DD4,"-"," "),"/"," ")," ",REPT(" ",25)),25))

First Part. OK
Second Part (by removing 0& from the 'Last Part' formula) is giving answer 6 for Data in DD4=MH / 123456 / 148. CORRECT answer=12346. How to get?
Please remember the answer cells are in column DI, DJ & DK. There are values in columns DE, DF, DG & DH.

 

[Sal Paradise]

First Part. OK
Second Part (by removing 0& from the 'Last Part' formula) is giving answer 6 for Data in DD4=MH / 123456 / 148. CORRECT answer=12346. How to get?
Please remember the answer cells are in column DI, DJ & DK. There are values in columns DE, DF, DG & DH.

If you could download the HTML maker in my signature and show us your sample data along with what answers you expect on a bigger sample set (maybe 20 lines?) it would help us understand the problem.

It seems like the request is changing from what you originally wanted, so please let us know what you're after.

 

[hsandeep]

I punched the "Data" in DD4 as DD4=MH / 123456 / 148.
Then copy pasted your formulas in DI4, DJ4 & DK4 to get First answer, Second answer & Third answer.
First answer is CORRECT.
But Second answer & Third answer is wrong.

CORRECT Second answer=148. How to get?
Correct Third answer=0148. How to get?

There are some arbitrary values in columns DE4, DF4, DG4 & DH4.

 

[Scott Huish]

Using mine, take out the TEXT part of the formula:

Excel Workbook
	DD	DE	DF	DG	DH	DI	DJ	DK
4	MH / 123456 / 148	*	*	*	*	MH123456148	148	0148
Sheet1

 

[hsandeep]

It works. Can I get the value before FIRST "/" or "-"? Here "MH ". There is 1 blank after MH since MH / 123456 / 148. Also, then, in another cell without blank (if any) i.e. only MH in this case.