Excel formulas giving Value Error in Mac

Asked Jun 13 '14 at 18:24

Active Jun 13 '14 at 18:24

Viewed 211 times

To preface, I have already tried the Ctrl+U and Command+Enter as detailed here: Excel formulas giving #VALUE! error when ported to Mac

Problem: Data ported from SAS stacks the two needed values into one cell. So I am using a step process to automatically split the cell and then concatenate based on desired values.

Here is the formula that works in PC, but showing a Value Error when trying to work with the spreadsheet in Mac Excel.

=LEFT('Data Input'!B7,FIND(CHAR(10),'Data Input'!B7)-1)
=RIGHT('Data Input'!B7,FIND(CHAR(10),'Data Input'!B7)-1)

edited May 23 '17 at 10:27

Community

asked Jun 13 '14 at 18:24

Jebediah15

4

If you switch `CHAR(10)` to `CHAR(13)` does it work? – Tim Williams Jun 13 '14 at 18:31
Yes, that works. What did that do? And, will [CHAR(13)] work on PC? – Jebediah15 Jun 13 '14 at 18:42
1

Your issue is related to historical differences between how Windows and Mac represent line breaks. `CHAR(13)` likely won't work in Win XL - typically that uses `CHAR(10)` http://en.wikipedia.org/wiki/Newline – Tim Williams Jun 13 '14 at 18:45
4

Try - `=LEFT('Data Input'!B7,IFERROR(FIND(CHAR(13),'Data Input'!B7),FIND(CHAR(10),'Data Input'!B7))-1)` – Tim Williams Jun 13 '14 at 18:50

Excel formulas giving Value Error in Mac

0 Answers0