Custom Cell Format

[AlexMihai]

Hello! The data i want to be displayed have diferent formats eg. "S-001" or "S-001.1" or " S-001/A" or "S-001.1/A".The "S-" part i want to be a prefix that will be added to text no matter what comes after it.Also the number that fallows the prefix should be 3 digits(if cell is filled with "1", excell should return "S-001").I have managed to do it with a custom format cells that looks like this : "S-"00#. But if cell is filled with "1.1", or "1/A", excell wont recognize it and it will return "S-001" instead of "S-001.1" or "S-001/A".Also if i fill cell with "1" i dont want the data to be displayed as "S-001.0".Is it posible to aply a single format to all the cells and my data to be displayed corectly?Thank you!

 

[Yongle]

Welcome to the forum
Provide several examples of typical sample data with raw data in column A and expected results in column B

XL2BB is a downloadable tool which allows you to post sample data for us

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[AlexMihai]

Book1
	A	B
1	Raw data	Expected results
2	1	S-001
3	1.1	S-001.1
4	1/A	S-001/A
5	1.1/B	S-001.1/B
6	234.1	S-234.1
7	234/B	S-234/B
8	234.1/B	S-234.1/B
Sheet1

Thank you!

 

[Yongle]

I cannot think of a simple way to achieve what you want
Are you prepared to use VBA ?

 

[AlexMihai]

Not really, but everything has a start.

 

[Yongle]

Let's wait 24 hours to see if someone comes along and finds a non-VBA solution for you

If not I will post VBA solution later tomorrow

 

[Dossfm0q]

Greetings
I Hobe to be helpful please feed me back

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER.

 =SUBSTITUTE(A2,--MID(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9,".",","},A2),"")),COUNT(--MID(SUBSTITUTE(SUBSTITUTE(A2,".",0),",",0),ROW($1:$99),1))),TEXT(--MID(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9,".",","},A2),"")),COUNT(--MID(SUBSTITUTE(SUBSTITUTE(A2,".",0),",",0),ROW($1:$99),1))),"""S-""000.##"))

Book1
	A	B
1	Raw data	Expected results
2	1.2b	S-001.2b
3	1.2	S-001.2
4	1/A	S-001./A
5	1.1/B	S-001.1/B
6	234.1	S-234.1
7	234/B	S-234./B
8	234.1/B	S-234.1/B
9
10
11
12
13
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=SUBSTITUTE(A2,--MID(A2,MIN(IFERROR(FIND({0;1;2;3;4;5;6;7;8;9;".";";"},A2),"")),COUNT(--MID(SUBSTITUTE(SUBSTITUTE(A2,".",0),";",0),ROW($1:$99),1))),TEXT(--MID(A2,MIN(IFERROR(FIND({0;1;2;3;4;5;6;7;8;9;".";";"},A2),"")),COUNT(--MID(SUBSTITUTE(SUBSTITUTE(A2,".",0),";",0),ROW($1:$99),1))),"""S-""000.##"))
Press CTRL+SHIFT+ENTER to enter array formulas.

Below For Other Regions Language using ;

=SUBSTITUTE(A2;--MID(A2;MIN(IFERROR(FIND({0;1;2;3;4;5;6;7;8;9;".";";"};A2);""));COUNT(--MID(SUBSTITUTE(SUBSTITUTE(A2;".";0);";";0);ROW($1:$99);1)));TEXT(--MID(A2;MIN(IFERROR(FIND({0;1;2;3;4;5;6;7;8;9;".";";"};A2);""));COUNT(--MID(SUBSTITUTE(SUBSTITUTE(A2;".";0);";";0);ROW($1:$99);1)));"""S-""000.##"))

 

[yky]

Greetings
I Hobe to be helpful please feed me back

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER.

I think OP doesn't want to change the underlying value of each cell, just displaying them in the desired format. As yongle pointed out, there is no one custom format that would do it. I think it takes three custom formats to do what the OP wants.

 

[Yongle]

@AlexMihai
Does the formula provided by @Dossfm0q achieve what you require ?

If not then ....
I cannot see any way to do this by simple formatting
VBA would replace the raw data with required results (either by overwrite or in an adjacent column)

Please let us know how you want to proceed

 

[yky]

VBA can do it without altering the underlying data.

I copied Col A to C, applied the VBA code to C, copied C and pasted by value to D, just to show that the underlying data is intact.

The VBA code, however, doesn't save much effort. If any of the following codes can be applied to a large area, say, C2:C100, then it would save some effort. If, however, every row has its own format, then VBA doesn't save much effort because you have to code for every row. That's not much different than setting custom format by hand.

Sub num_format()
Range("C2").NumberFormat = """S-""000;;;"
Range("C3:C6").NumberFormat = """S-""000.0;;;""S-00""@"
Range("C7:C8").NumberFormat = ";;;""S-""@"
End Sub

Doc.xlsm
	A	B	C	D
1	Raw data	Expected results	Copied from A, after VBA	Copy C and paste to D by value
2	1	S-001	S-001	1
3	1.1	S-001.1	S-001.1	1.1
4	1/A	S-001/A	S-001/A	1/A
5	1.1/B	S-001.1/B	S-001.1/B	1.1/B
6	234.1	S-234.1	S-234.1	234.1
7	234/B	S-234/B	S-234/B	234/B
8	234.1/B	S-234.1/B	S-234.1/B	234.1/B
Sheet2