+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    09-05-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    12

    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. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,747

    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)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    09-05-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    12

    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
    Last edited by Janning197; 12-18-2015 at 01:07 PM.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

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

    Try this one and see if it works

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,747

    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 about =mid(o20,2,5)?
    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. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,747

    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. #7
    Registered User
    Join Date
    09-05-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    12

    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. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,747

    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. #9
    Registered User
    Join Date
    09-05-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    12

    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. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,747

    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. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    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. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,747

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

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

  13. #13
    Registered User
    Join Date
    09-05-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    12

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. general excel issue
    By guanqiao in forum Excel General
    Replies: 1
    Last Post: 08-21-2015, 10:21 AM
  2. Replies: 6
    Last Post: 09-20-2013, 02:40 PM
  3. General Excel Search Tool for all the functions !
    By Studlyuan2003 in forum Excel General
    Replies: 2
    Last Post: 06-30-2013, 06:41 PM
  4. Excel General Issue
    By TAMILARASAN in forum Excel General
    Replies: 1
    Last Post: 05-30-2012, 04:26 AM
  5. TRIM, LEFT and other excel formula functions in VB
    By ChrisMattock in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-19-2010, 10:00 AM
  6. repost from general area ref values with column to left HELP
    By learningasigo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-25-2006, 02:00 AM
  7. Macro for removing some parts of data, using LEFT/RIGHT functions
    By Sach in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2006, 03:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1