Master Excel formulas and functions—once and for all. Get tips for using the most challenging of the 450+ functions in Excel for Mac 2016. In the second drop-down, click on the last option Use a formula to determine which cells to format. Type the formula provided above into the space provided. Click on the Format with: drop-down then select the Light Green Fill with Dark Green Text option. Good morning experts! Users send in tickets that require a MAC address. They come in all different kinds of formats: XX:XX:XX:XX:XX:XX XX.XX.XX.XX.XX.XX XXXX.XX.XX.XXXX XXXX.XXXXXX.XX XX-XX-XX-XX-XX-XX XXXXXXXXXXXX. You get the idea. So we copy and paste the info from the ticket into a spreadsheet for processing and then work of the spreadsheet to do them in bulk. In one application, we have to enter the mac in all lower case letters with no special characters. (xxxxxxxxxxxx) Then in another application, we have to enter the MAC in all upper case letters with: as separators. (XX:XX:XX:XX:XX:XX) So I would like to paste into column A however it is in the ticket and then have column B automatically remove any special characters and make all lower case. Then Column C would automatically make them upper case and add the: where needed. Does anyone know a formula to make this happen? I am using Excel 2007 (MS Office Professional) Thank you! Hi Irrylyn, To make it all lower case letter, you can use this combination of LOWER() and SUBSTITUTE(). I only wrote it to remove:., but if you have more characters to remove just add more substitute shells: =LOWER(SUBSTITUTE(SUBSTITU TE(SUBSTIT UTE(A1,':','),'.' ,' '),'-',') ) To split by every 2 characters, i just used a few MID functions =UPPER(MID(B1,1,2)&':'&MID (B1,3,2)&':'&MID(B1, 5,2)&':'&M ID(B1,7,2) &':'&MID(B 1,9,2)&':' &MID(B1,11,2)) Change B1 to whatever your first formula is to make lower case. Thank you Matt. That works perfectly! Bluestacks for mac download 2016. I just wanted to add a substitute for space or comma also so ended up with: =LOWER(SUBSTITUTE(SUBSTITU TE(SUBSTIT UTE(SUBSTI TUTE(SUBST ITUTE(A2,':','),'.' ,'),'-',' '),' ','),',',')) and then for my C column, just how you had it: =UPPER(MID(B2,1,2)&':'&MID (B2,3,2)&':'&MID(B2, 5,2)&':'&M ID(B2,7,2) &':'&MID(B 2,9,2)&':' &MID(B2,11,2)) Makes perfect sense now that I can read it. ![]() I didnt realize you could nest those functions. Note for others who might find this useful: Make sure you format the columns to 'General' =). I have a D column which consists of on of four grade values: A, B, C, or D in each row. The rows will be marked or unmarked in Column C with an 'X'. I would like Column D to be conditionally formatted (red fill/ text) based on the criteria that its value is 'C' and its row is marked with 'X' in Column C. Example: B C D E C D X A X C Highlight Cell Rules>More Rules. And then wrote an AND formula but can not get it to work. I am either prompted to 'enter a valid formula' or get no result. Perhaps I just need help with the formula? But the process and interface is quite different from how I did this in windows I am not even sure I am in the right menu.
0 Comments
Leave a Reply. |