Text stripper with 2 more tuning

[makinmomb]

I have this on b

CL-0497 (002-01) # 2950/ 2850
CL - 0518 #2950
CL 0766 # 2250

I have this results on C

CL497(0021)
CL518
CL0766

My formula is

Please Login or Register  to view this content.

[makinmomb]

the actual result I need is

CL497
CL518
CL766

I want a two more requirements , I want the formula to destroy brackets and then I want it to insert - sign after where not there example on

CL 0766 # 2250

[shukla.ankur281190]

Then What is you requirement ?????

[shukla.ankur281190]

Try

C1=TRIM(SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(B1,SEARCH("#",B1)-2)," ",""),"-0",""),"(",REPT(" ",255)),50),"0",""))

[makinmomb]

It not takes care of the follow

CM-0070 (84494-EB4)LIT #12900
CM-0105 (702873) LIT#12900
CM-0106 (PWC84599-BFO) INTRUDER #12900
CM - 0111 (P705962) YORKTOWN #12900
CM-0118 (708559) PORTER #13400
CM- 0122 (P708025)HOLTON ST #15900
CM-0126 (705170) #84/12900
CM-0127 (705169)YORKTOWN #119/12900

Results expected

CM106
CM111
CM118
CM122
CM126
CM127

[shukla.ankur281190]

For this CM-0070 (84494-EB4)LIT #12900

Result :- CM0070 or CM7

Pls confirm??

[makinmomb]

Result need be CM007

I think use of helper may be required

First to get the - sign
Then to destroy brackets and words before # sign

[JohnTopley]

Try

=LEFT(TRIM(SUBSTITUTE((LEFT(A1,FIND("(",A1)-1)),"-","")),2)&(RIGHT(TRIM(SUBSTITUTE((LEFT(A1,FIND("(",A1)-1)),"-","")),3))

[Khalidngo]

Or this:

=REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,MID(A2,FIND("(",A2),99),""),"-","")," ",""),3,1,"")

Regards,

[JohnTopley]

Better ..

=TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("(",A1)-1)," ",""),"-0",""))

[samba_ravi]

attach a sample excel file with expected result

[Khalidngo]

Nice John Topley

Even shorter version of my formula # 9:
=REPLACE(SUBSTITUTE(REPLACE(A2,FIND("(",A2),99,"")," ",""),3,2,"")

Regards,

[makinmomb]

Thank you all , I will use two formula , my original , and one that I got here

[makinmomb]

Cm-0070 (84494-eb4)lit #12900
cm-0105 (702873) lit#12900
cm-0106 (pwc84599-bfo) intruder #12900
cm - 0111 (p705962) yorktown #12900
cm-0118 (708559) porter #13400
cm- 0122 (p708025)holton st #15900
cm-0126 (705170) #84/12900
cm-0127 (705169)yorktown #119/12900
cm - 0128 #8400

[makinmomb]

I want to combine the given formula with what I got here

Please Login or Register  to view this content.

[makinmomb]

If the text has brackets to use
=REPLACE(SUBSTITUTE(REPLACE(A2,FIND("(",A2),99,"")," ",""),3,2,"")
Otherwise
=SUBSTITUTE(SUBSTITUTE(LEFT(A2,SEARCH("#",A2)-1)," ",""),"-0","")

If it does correctly it should convert these

cm-0126 (705170) #84/12900
cm-0127 (705169)yorktown #119/12900
cm - 0128 #8400

to

CM126
CM127
CM128

[JohnTopley]

Try

=TRIM(LEFT((SUBSTITUTE(SUBSTITUTE(A1," ",""),"-0","")),5))

[makinmomb]

Thanks it takes cares of both criteria , ha ha ha , you made my day