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

 

[Sal Paradise]

Excel 2003
	C	D	E	F
3	Data	Full String	Last Number	Last Number with Zero
4	MH / 456789 - 187	MH456789187	187	0187
5	YH / 1237 - 000	YH1237000	000	0000
Sheet1

Cell Formulas
Range	Formula
D4	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C4,"/",""),"-","")," ","")
E4	=TRIM(RIGHT(SUBSTITUTE(C4,"-",REPT(" ",50)),50))
F4	="0"&E4

 

[hsandeep]

Thanks. It WORKS.

PLEASE HELP ME BY REPLYING TO MY THREAD
http://www.mrexcel.com/forum/showth...ertical-data&p=3187252&viewfull=1#post3187252

 

[hsandeep]

I re-checked & it did not work.

Instead of C4, cell was DD4
Instead of D4, cell was DR4. Output was ok here.
But in cell DS4, the output was same as DD4. Last number did not appear.
There are values in DD4:DQ4.
What to do?

 

[Rick Rothstein]

Try these formulas...

First Part:
=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&REPLACE(TRIM(SUBSTITUTE(SUBSTITUTE(DD4,"/"," "),"-"," ")),3,1,"")," ",REPT(" ",25)),50),25))

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

 

[hsandeep]

Try these formulas...

First Part:
=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&REPLACE(TRIM(SUBSTITUTE(SUBSTITUTE(DD4,"/"," "),"-"," ")),3,1,"")," ",REPT(" ",25)),50),25))

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

Last Part: OK
First Part: is not giving Last Number in the full string.
Second Part: It is Last Number. How to generate?

 

[Rick Rothstein]

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))

 

[hsandeep]

Rick,
need second part also i.e. last number WITHOUT suffixing 0.

 

[Rick Rothstein]

Rick,
need second part also i.e. last number WITHOUT suffixing 0.

Just remove the 0& from the front of the formula... that is what is putting the leading zero on the number.

 

[Scott Huish]

Rick,
need second part also i.e. last number WITHOUT suffixing 0.

I don't think anyone suffixed 0, but prefixed it with 0 as per your original request.

Excel Workbook
	DD	DE	DF
4	MH / 456789 - 187	MH456789187	0187
5	YH / 1237 - 000	YH1237000	0000
6	YH/1237-489	YH1237489	0489
7	YH/1237-2489	YH12372489	2489
Sheet1