# Issue with Text data (as General) and Left / Right Functions in Excel.

1. ## Issue with Text data (as General) and Left / Right Functions in Excel.

I have an excel spread sheet that I'm having issues on a cell using functions. The cell contains an account number (General format) which can be from 00000 to 99999
Most are 12140 for example (cell O20 ).
When I do a =LEFT(O20,5), I get "1214" ....the "5" is missing.
When I do a RIGHT(O20,5) I get "2140"....the "1" is missing.
Can anyone tell me what is going on here?
Thanks Much - James

2. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

Does the cell contain the number 12140 or the text string " 12140 " (with spaces or other non-printing character at the front and back)?

What do you get if you include the TRIM() or CLEAN() functions? =left(trim(O20),5)

3. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

Hi,

I forgot to mention, I've tried the TRIM and CLEAN, separately and together...even tried:
=LEFT(CLEAN(TRIM(O22)),5) I get the same issue.
The cell is in the general format, I've tried changing it to TEXT and to NUMBER...same issue occur.
As best I can tell there are no spaces in the data.
on cell O21 which is 12199, I just changed 12199 to be >12199‬< and did a =LEFT(CLEAN(TRIM(O21)),5)....I get >121
Thanks James

4. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

Try this one and see if it works

Formula:
`Please Login or Register  to view this content.`

5. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

Other possible debugging functions: =istext(o20) this does not test for text vs number formats, but tests if the actual cell value is being seen as text or not.
=len(o20) If there really are only the 5 characters that you indicate, this should return 5. If it returns anything other than 5, it suggests that there are additional characters of some kind at the front.
What do you get from =code(left(o20,1))? What do you get from =code("1")? If these two do not return the same value, then there must be another character in front of the 1.
Feel free to look through the other available text functions (https://support.office.com/en-us/art...__toc309306717 ) and see if any others may help debug this.

One way or another, your description of the problem suggests that there must be additional characters at the front and back of the cell's value, and the solutions will be about identifying and removing those extra characters. I don't think there is much specific we can do from this side of the internet without a sample spreadsheet that exhibits the problem.

6. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

As I consider Alkey's suggestion, I find this page: https://support.office.com/en-us/art...rs=en-US&ad=US Clean and Trim apparently only remove a small subset of all possible non-printing characters. The SUBSTITUTE() function as he describes appears to be the preferred way to detect and remove those non-printing characters that Clean and Trim do not remove.

I learned something today.

7. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

=LEFT(SUBSTITUTE(O20,CHAR(160),""),5)
has the same issue.
Doing a LEN(O20) is showing 7 long...so there are 2 extra characters there, I can not see.

8. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

This is where the =CODE() function should be useful. What do you get from =CODE(LEFT(O20,1))? Alkey's suggestion should still work, you would need to substitute the actual code number of the character for the 160.

9. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

istext shows "TRUE"
=code(left(o20,1)) is showing 63,
=code("1") is showing 49

10. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

Interesting =CHAR(63) produces a question mark ? character. I'm a bit surprised that it is not visible.

11. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

You may also try to replace code 160 with code 29

12. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

This might be a useful reference: http://www.asciitable.com/

13. ## Re: Issue with Text data (as General) and Left / Right Functions in Excel.

I may have resolved this. Oracles Fusion, BI publisher is producing this xls. In BI publisher I'm using a LTR function (Left to Right), it seems this was producing a hidden character.
Thanks James

There are currently 1 users browsing this thread. (0 members and 1 guests)