Modify and Extract Text ( Substitute)

[nburaq]

Hi Gents,
I have table shown below and I would like to format the text shown in column A like in column B. I have done this by extremely crazy formula took almost 1 hour to do it but I am pretty sure there are some clever guys who can do this in a shorter way ! PS: be careful with quotation marks in cells also words before numbers may vary ( I have managed to do it with two words before number starts)
Good Luck Gents!

sample.xlsx
	A	B
1	Text	Mod Text
2	Line 2-NC-81-806-D21	2"-NC-81-806-D21
3	Gas Line 4"-NG-81-003-F83S	4"-NG-81-003-F83S
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=IF(COUNTIF(A2,"*"&CHAR(34)&"*")=0,SUBSTITUTE(IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,MID(TRIM(A2),1+FIND("~",SUBSTITUTE(TRIM(A2)," ","~",1)),255),MID(TRIM(A2),1+FIND("~",SUBSTITUTE(TRIM(A2)," ","~",2)),255)),"-","""-",1),MID(TRIM(A2),1+FIND("~",SUBSTITUTE(TRIM(A2)," ","~",2)),255))

 

[Fluff]

How about

=FILTERXML("<k><m>"&SUBSTITUTE(IF(ISNUMBER(FIND(CHAR(34),A2)),A2,SUBSTITUTE(A2,"-","""-",1))," ","</m><m>")&"</m></k>","//m[position()=last()]")

 

[jasonb75]

A bit shorter,

Book1
	A	B
1	Text	Mod Text
2	Line 2-NC-81-806-D21	2"-NC-81-806-D21
3	Gas Line 4"-NG-81-003-F83S	4"-NG-81-003-F83S
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)),"""-","-"),"-","""-",1)

 

[nburaq]

A bit shorter,

Book1
	A	B
1	Text	Mod Text
2	Line 2-NC-81-806-D21	2"-NC-81-806-D21
3	Gas Line 4"-NG-81-003-F83S	4"-NG-81-003-F83S
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)),"""-","-"),"-","""-",1)

Thanks for the solutions both method worked very well. except one thing I dont know why quotation marks sometimes appears double like 4"" for some rows. Is it possible to help me for this problem as well? Thanks again!

 

[jasonb75]

sometimes appears double like 4"" for some rows. Is it possible to help me for this problem as well?

Not without seeing it. The formula was based on the example provided, if the results are inconsistent then it would suggest that there is an error or inconsistency in the data that we do not know about.

 

[Fluff]

Maybe they are a pair of apostrophes, try this

=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"""-","-"),"-","""-",1),"''","")," ","</m><m>")&"</m></k>","//m[position()=last()]")

 

[jasonb75]

Maybe they are a pair of apostrophes,

Good point, I was thinking 0 width space between the original " and - as a possibility, hadn't thought of it being 2 singles instead.

 

[Fluff]

I was thinking 0 width space between the original " and -

Yup, that's another possibility We'll just have to wait for the OP to supply some more examples.

 

[nburaq]

Good point, I was thinking 0 width space between the original " and - as a possibility, hadn't thought of it being 2 singles instead.

Gentlemen's,
Once again what a great community and people. I am really happy to be a member of this forum! Good news, both formulas work perfect. I have checked the formulas on a clean page again and no issue so far. Thanks again for your help and concern.

 

[Fluff]

Glad it's sorted & thanks for the feedback.