Formatting text with " (inch symbol) in a column

[nburaq]

Hi Gents,
I am busy with formatting a text to a inch version if a text starts with digits . I could not figure out how to do it ( tried combination of left right substitute find search replace.....) Actually, later I noticed that excel somehow automatically fills the exact formatting style after filling a few columns. Anyway, I am obsessed to do it with a formula I guess Thanks in advance for all comments and help!

NL-81-B-ICD0-000431_4.0_ASB_2020-12-01_02.xlsx
	F
11
Sheet3

 

[jasonb75]

Your mini sheet came up blank so we can't see what you've tried. Does this help at all?

Book1
	A	B
1	123	123"
Sheet1

Cell Formulas
Range	Formula
B1	B1	=A1&""""

 

[nburaq]

Your mini sheet came up blank so we can't see what you've tried. Does this help at all?

Book1
	A	B
1	123	123"
Sheet1

Cell Formulas
Range	Formula
B1	B1	=A1&""""

Sorry for my bad. Here is the correct one

Short	Short Fixed
10-FS-81-016-B67	10"-FS-81-016-B67
12-FS-81-001-B67	12"-FS-81-001-B67
12-FS-81-002-B67	12"-FS-81-002-B67
12-FS-81-018-B67	12"-FS-81-018-B67
14-FS-81-002-B67	14"-FS-81-002-B67
145-FS-81-019-B67	145"-FS-81-019-B67
8-FS-81-027-B67	8"-FS-81-027-B67
9-FS-81-035-B67	9"-FS-81-035-B67

ps: one thing i really like is how excel understands and converts in short column 3_4 to 3/4" which is a correct notation

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B
1	Short	Short Fixed
2	10-FS-81-016-B67	10"-FS-81-016-B67
3	12-FS-81-001-B67	12"-FS-81-001-B67
4	12-FS-81-002-B67	12"-FS-81-002-B67
5	12-FS-81-018-B67	12"-FS-81-018-B67
6	14-FS-81-002-B67	14"-FS-81-002-B67
7	145-FS-81-019-B67	145"-FS-81-019-B67
8	8-FS-81-027-B67	8"-FS-81-027-B67
9	9-FS-81-035-B67	9"-FS-81-035-B67
Main

Cell Formulas
Range	Formula
B2:B9	B2	=SUBSTITUTE(A2,"-","""-",1)

 

[jasonb75]

Sorry for my bad. Here is the correct one

As you have XL2BB installed it would be better if you posted it correctly with that. When you copy and paste as you have above it corrupts the formatting and doesn't copy back to excel correctly.

 

[nburaq]

How about

+Fluff 1.xlsm
	A	B
1	Short	Short Fixed
2	10-FS-81-016-B67	10"-FS-81-016-B67
3	12-FS-81-001-B67	12"-FS-81-001-B67
4	12-FS-81-002-B67	12"-FS-81-002-B67
5	12-FS-81-018-B67	12"-FS-81-018-B67
6	14-FS-81-002-B67	14"-FS-81-002-B67
7	145-FS-81-019-B67	145"-FS-81-019-B67
8	8-FS-81-027-B67	8"-FS-81-027-B67
9	9-FS-81-035-B67	9"-FS-81-035-B67
Main

Cell Formulas
Range	Formula
B2:B9	B2	=SUBSTITUTE(A2,"-","""-",1)

Perfect Fluffy! if there is 3_4 kind of numbering how can I convert from 3_4 to 3/4" ? Think like 3_4-FS-20-032 to 3/4"-FS-20-032.
Thanks again

 

[Fluff]

How about

=SUBSTITUTE(SUBSTITUTE(A2,"-","""-",1),"_","/")

 

[nburaq]

How about

=SUBSTITUTE(SUBSTITUTE(A2,"-","""-",1),"_","/")

Thanks a lot! Perfect!

 

[nburaq]

How about

=SUBSTITUTE(SUBSTITUTE(A2,"-","""-",1),"_","/")

But there must be IF somewhere right?

 

[Fluff]

Thanks a lot! Perfect!

Glad we could help & thanks for the feedback.

But there must be IF somewhere right?

Nope, no need.