User Profile
Katoomba
Copper Contributor
Joined Dec 06, 2020
User Widgets
Recent Discussions
Re: Keep conditional formatting range when inserting/deleting cells/rows/columns?
I have been experimenting with a rough workaround for this problem that applies if you only need to insert new rows OR columns but not both If you set the conditional format range as entire rows eg ($5:$50), you can then insert new rows within this range to automatically expand it to ($5:$51) without it breaking into pieces. But: - It only works for conditional formats based on a formula; however, you can mimic (for example) traffic light icons by using character "l" in the Wingdings font and then applying a custom number format of l;l;l;@ on the required range. This forces the values 1, 0 and -1 to all display a filled circle, with the ability to then use conditional formatting to change the colour based on the underlying number value - You will likely need to include an AND statement in every conditional format formula to stop it from formatting cells in unwanted columns outside your desired range. eg = AND(A$1="Y") and put "Y" in row 1 for all columns that require the format - Select "Stop if true" on each formula to help improve performance - The approach will only work on either Rows OR Columns. For example switch to a range of (say) $B:$Z if you wish to add columns within this range95KViews0likes0CommentsRe: Formula for extracting nth field from long delimited text string
Riny_van_Eekelen Thank you for your response and my apologies for not replying earlier as I only picked up the discussion now. Thank you for your suggestion, but your formula is probably a bit long and complicated for my purposes I am currently using a large delimited string (eg >15K) to pass data between > 20 networked workbooks and a central master workbook (ie a single string greatly simplifies links between workbooks). I am then using the MID formula many thousands of times to automatically extract data at the other end. MID works surprisingly well (with some performance overhead), but I am hoping that there is a simpler standard formula that ideally would only require parameters to be included once or uses fewer SUBSTITUTES. I will ultimately need to write up and handover the workbooks to someone else and would like all formulae to be simple enough for someone with middling Excel skills to understand - ie no VBA or array formulae Excel provides the Textjoin and Concatenate formulae to create delimited strings but unfortunately offers no simple formula to extract data from a delimited string Excel on the Web compatibility is not required (at this point) but is a nice to have4KViews0likes0CommentsFormula for extracting nth field from long delimited text string
I'm currently using the following formula to extract the nth field (n=7) from a text string located in cell A1 and delimited by » Char(187): =MID(A1,FIND("®",SUBSTITUTE(A1,CHAR(187),"®",7))+1,FIND("®",SUBSTITUTE(A1,CHAR(187),"®",7+1))-(FIND("®",SUBSTITUTE(A1,CHAR(187),"®",7))+1)) It works, but is there a simpler formula that is more inefficient as I am using the formula many times? A1 sample: »44165»No»44166»»Design & implement a new XXX system»»Insert Name» Excel 2016 & Windows, but would prefer open platform solution (eg Excel for web); no vba please I have looked around and also tried: TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1))). This formula is simpler, but doesn't seem to work on large strings > 256 chars FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE(E51,"&","&"),CHAR(187),"</s><s>") & "</s></t>","//s["&E50&"]") This formula has reserved chars (eg &) and does not work on web excel4.9KViews0likes3Comments
Recent Blog Articles
No content to show