jdellasala
Well-known Member
- Joined
- Dec 11, 2020
- Messages
- 755
- Office Version
- 365
- Platform
- Windows
- Mobile
- Web
I first ran into this a day or two ago. I copied data from a post that included a BB Board table like this:
When I pulled the data into Excel by a simple Copy/Paste (match destination), the data LOOKED ok although numeric data was left justified, but no matter what I did I was unable to convert numeric data like in columns A and B above into numbers. It happened again today, so I investigated further.
It ends up that what should be a completely visible question mark is appended to the end of every cell, so that the second row in Column A above comes in with a LEN() of 2 instead of 1, but the second character is invisible.
When I looked at the CODE() value of each character in the cell, the second character CODE was 63 which SHOULD be a simple question mark.
The easiest way I found to convert the value to a number was to use
Is this a Board issue, or something that Microsoft needs to address?
Column A | Column B | Column C |
1 | 12/18/2022 | Grapes |
203 | 12/17/2022 | Oranges |
It ends up that what should be a completely visible question mark is appended to the end of every cell, so that the second row in Column A above comes in with a LEN() of 2 instead of 1, but the second character is invisible.
When I looked at the CODE() value of each character in the cell, the second character CODE was 63 which SHOULD be a simple question mark.
The easiest way I found to convert the value to a number was to use
Excel Formula:
=NUMBERVALUE(LEFT(A2,LEN(A2)-1))
Last edited by a moderator: