Consulting

Results 1 to 19 of 19

Thread: Left/Right formula not working

  1. #1
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location

    Left/Right formula not working

    Hi all,

    I would like to ask for solution as my left/right formula isn't working.

    eg. I20070095 <- I need to use right formula to capture the last 3 digits. Formula: =right(cellA1,3)

    Somehow the end result appear as 009 instead of 095.

    I have checked, there is no hidden space anywhere. But seems like in one of the numbers, there is an additional character in it.

    How do I solve such issue?
    Last edited by Owl; 12-23-2020 at 04:32 AM.

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    PHP Code:
    =left(cellA1,3
    is invalid

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. For the last 3 I think you want RIGHT()

    2. The syntax was not correct; just use the reference cell address

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by snb View Post
    PHP Code:
    =left(cellA1,3
    is invalid
    Seems to work fine here:
    2020-12-23_103058.png
    (just teasing)

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by p45cal View Post
    (just teasing)


    Don't do that to me
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    @p45cal

    So, you'd better keep using it for the last 3 digits......

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by snb View Post
    @p45cal

    So, you'd better keep using it for the last 3 digits......

    That's RIGHT()
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    Since people are trying to confuse you with left or right, use Mid to avoid the confusion.
    =TEXT(MID(B3, LEN(B3)-2,3),"000")

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by jolivanes View Post
    Since people are trying to confuse you with left or right, use Mid to avoid the confusion.

    Disagree.

    While the OP's original post used LEFT() in the formula, the required results clearly were by using RIGHT()

    The corrected post #1 and it's expected results now use RIGHT()

    IMO (no 'H' ) if you need the right n characters, the code is clearer if you use RIGHT()

    https://www.aivosto.com/articles/stringopt2.html

    Left$, Right$ and Mid$. Performance keeps at the degraded level with this group of functions. These functions create new strings by copying some characters in the input string. These are the only functions that can access the individual characters in a string. As you can see, Mid$ is slower than Left$ or Right$. This means you should use Left$ and Right$ when possible and only resort to Mid$ when you really need to access characters in the middle.
    MID() + LEN() + TEXT() is slower than just using RIGHT()

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    It was all tongue in cheek Paul but interesting what you showed.
    I have such a feeling that it will not make much of a difference in actual computing time unless the OP has a gigantic file to do.
    But saying that, we should strive to give a proper code, which will be open for discussion also. Ask 2 other people that answered in this thread and I would say that we'll end up with different opinions.
    Never mind that IMHO code should be so that the OP can change it if and when needed, even if it would be slightly slower in milliseconds. In this case that does not come play though.
    A happy new year to all of you and we hope that they will get the Covid-19 problem under control sooner rather than later.
    Stay safe.

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by jolivanes View Post
    It was all tongue in cheek Paul but interesting what you showed.
    Ahhh - must be my cabin fever

    Too many reruns and I've lost my sense of humor
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    Sorry I made a mistake in my first post

    Column A is imported data from folders name; I did some changes to amend the path name hence leaving only the name of folders.
    In order to know how many lines to auto create, I need to use left and right function.
    For I20050060-063 as shown, the formula is correct and my VBA is able to auto generate the number of rows for 61,62 and 63.
    Capture1.JPG

    However, for below ‎I‎20050066-068, my left function doesn't work. The last 2 digits 66 is missing.
    By right for my column D for Right function, it should appear as 066 instead of 500.

    There is no space in between the numbers and alphabet too.
    I tried using TRIM function, it does not work too.

    Capture2.JPG


    I managed to solve the issue by renaming the folder name and import the path name to excel again. But this is too troublesome if such method is needed to solve the error.

    What I found out was - I place my cursor on column A ‎I‎20050066-068 and use the right directional key, the alphabet I and number 2 in front, I need to tap on my right directional key twice before it goes to the next number on the right. As for the other numbers, I only need to hit my right directional key only once.
    I think that's why left function isn't able to capture the last 2 digits.

    I would like to understand why it happened and how do I solve this issue in excel.

  13. #13
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    It looks like there are invisible characters in the folder name.
    Why don't you upload an Excel file with just this portion of the sheet you're showing pictures of? A bit of detective work needed.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  14. #14
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I have a utility 'cleaning' sub that I can run on a Range.

    I made it into a UDF which is not as efficient as a Sub run on multiple cells


    Option Explicit
    
    
    
    
    Function CleanLeftString(S As String, N As Long) As String
        Dim c As Long, i As Long
        Dim aryChars As Variant
        
        'The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII
        'code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting
        'characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove
        'these additional nonprinting characters
        S = Application.WorksheetFunction.Clean(S)
        
        'replace CR, NL, and tab with space, and 127, 129, 141, 143, 144, 157 and 160 with space
        aryChars = Array(9, 13, 10, 127, 129, 141, 143, 144, 157, 160)
        For i = LBound(aryChars) To UBound(aryChars)
            If InStr(S, Chr(aryChars(i))) > 0 Then Call Replace(S, Chr(aryChars(i)), " ", xlPart)
        Next i
            
        'WS TRIM() removes multiple spaces, VBA.Trim does not
        If InStr(S, Chr(32)) > 0 Then S = Application.WorksheetFunction.Trim(S)
    
        CleanLeftString = Left(S, N)
    End Function
    Edit - Added Left()
    Last edited by Paul_Hossler; 01-15-2021 at 07:59 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #15
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    I attached the excel here, filtered those highlighted in green which has hidden characters inside.
    Attached Files Attached Files

  16. #16
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    There are extra character(s) in your green cells in column A, unicode 8206. You can get rid of them with Find & Replace, although it might be very difficult to enter that character into the Find what: field.
    You could select the column and run a one-liner macro to do the find and replace:
    Sub blah()
    Selection.Replace What:=ChrW(8206), Replacement:="", LookAt:=xlPart
    End Sub
    or execute the single line:
    Selection.Replace What:=ChrW(8206), Replacement:="", LookAt:=xlPart
    in the Immediate pane of the VBE.
    The character seems to be a left-to-right mark: https://www.codetable.net/decimal/8206
    and/or a Han character: https://www.fileformat.info/info/uni...8206/index.htm

    You may be able to solve this one more easily by how you get the data into the Excel sheet in the first place; how are you doing this?
    There are, for example, some settings you can tweak when you import data/csv files etc.

    ps. you could also change the formulae which look at the cells with these characters in, for example:
    =LEFT(A15,9)
    could become:
    =LEFT(SUBSTITUTE(A15,UNICHAR(8206),""),9)
    but if there's a danger other unicode characters apart from 8206 could make their appearance this could get cumbersome. Better to import the data cleanly in the first place.
    Last edited by p45cal; 01-21-2021 at 06:04 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  17. #17
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    Thank you! I used the formula and it works!

    Those are the name of folders which I copy path to the excel, Crtl+H to remove the path name leaving only the name of folders.

    Can you guide me how do you find out the code 8206?

    So far I tested quite a few excel with different months of folder names, the hidden character is code 8206.

  18. #18
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Looking at just one of the green cells, and as you thought in your msg#1, there were extra characters, so I wanted to see what they were, so I used the formula shown in column Y in the picture below, in column J and copied across as far as needed:
    =MID($A43,COLUMN()-8,1)
    This shows you one character per cell, and as you can see the extra characters appear blank. So I moved onto the next step which was to show the ascii code of the characters:
    =CODE(MID($A43,COLUMN()-8,1))
    This showed those characters to be 63, which is usually a question mark. Clearly not. So the next step was to show the unicode code of the characters:
    =UNICODE(MID($A43,COLUMN()-8,1))
    This revealed the 8206 character.
    2021-01-22_121738.jpg
    Last edited by p45cal; 01-22-2021 at 08:07 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  19. #19
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    It might not a difference, but if there's a chance of other UNICODE characters


    Option Explicit
     
    'https://stackoverflow.com/questions/37024107/remove-unicode-characters-in-a-string
    Public Function StripNonAsciiChars(ByVal InputString As String, N As Long) As String
        Dim s As String
        With CreateObject("VBScript.RegExp")
            .Global = True
            .MultiLine = True
            .IgnoreCase = True
            .Pattern = "[^\u0000-\u007F]"
            s = .Replace(InputString, "")
        End With
        
        With Application.WorksheetFunction
            s = .Clean(s)
            StripNonAsciiChars = .Trim(s)
        End With
    End Function
    
    Capture.JPG
    
    Capture1.JPG
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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