Custom Cell Format

AlexMihai

New Member
Joined
Jul 28, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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!
 
OK, maybe we can use IF statement to test the data to decide which number format to apply. To do that we need representative data of every kind. Note sure if OP has provided that.
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sorry If too late

VBA Code:
=IFERROR(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))),IF(IFERROR(SEARCH(".",--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))),1),0)<>0,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.#####"),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"))),"")


Händelse kalender skapare.xlsm
ABCD
1Raw dataExpected resultsFormlua ResultVerification Column B = C
21S-001S-001TRUE
31.1S-001.1S-001.1TRUE
41/AS-001/AS-001/ATRUE
51.1/BS-001.1/BS-001.1/BTRUE
6234.1S-234.1S-234.1TRUE
7234/BS-234/BS-234/BTRUE
8234.1/BS-234.1/BS-234.1/BTRUE
9 
10 
Sheet2
Cell Formulas
RangeFormula
D2:D8D2=B2=C2
C2:C10C2=IFERROR(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))),IF(IFERROR(SEARCH(".",--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))),1),0)<>0,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.#####"),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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top