Forum Discussion
Solved
Writing an IF function with multiple values in one cell
Hello, This is my first post here. Please let me know if this is correct! Device: PC/Windows 10 Pro 64-bit (Version 21H1 Build 19043) Excel version: Microsoft® Excel® for Microsoft 365 MS...
In B2:
=IF($A2="","",--ISNUMBER(FIND(","&COLUMN()-1&",",","&$A2&",")))
Fill to the right, then down (or vice versa) as far as needed.
See the attached version.
In B2:
=IF($A2="","",--ISNUMBER(FIND(","&COLUMN()-1&",",","&$A2&",")))
Fill to the right, then down (or vice versa) as far as needed.
See the attached version.
- Hi Hans! This is fantastic! Thank you. I figured out how to use the formula in other columns- my actual data is in column "I" instead of "A". I just changed the number in COLUMN()-1 to COLUMN()-10. Is that a proper way?
Also, do you know a way to populate the cell with the actual value instead of the formula listed?If the data are in column I instead of column A, I'd expect you would have to use COLUMN()-9 instead of COLUMN()-1 since column I is 8 columns to the right of column A, but the exact formula depends on the setup.
If you see a formula in the cell instead of its result:
- Make sure that the cell with the formula is NOT formatted as Text. If it is, change the number format to General, then press F2 and Enter to re-enter the formula.
- Make sure that 'Show Formulas' on the Formulas tab of the ribbon is off.
- One other thing- would you be able to walk me through the syntax and what each component does? Thanks so much!
