Left/Right formula not working

[Owl]

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?

[snb]

=left(cellA1,3)
is invalid

[Paul_Hossler]

1. For the last 3 I think you want RIGHT()

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

27620

[p45cal]

=left(cellA1,3)
is invalid

Seems to work fine here:
27622
(just teasing)

[Paul_Hossler]

(just teasing) :devil2::devil2::devil2:

Don't do that to me :doh::doh::doh:

[snb]

@p45cal

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

[Paul_Hossler]

@p45cal

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


That's RIGHT() :rofl::rofl::rofl:

[jolivanes]

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")

[Paul_Hossler]

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' :rotlaugh:) 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()

27659

[jolivanes]

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.

[Paul_Hossler]

It was all tongue in cheek Paul but interesting what you showed.

Ahhh - must be my cabin fever :banghead::banghead:

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

[Owl]

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.
27732

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.

27733


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.

[p45cal]

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.

[Paul_Hossler]

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()

[Owl]

I attached the excel here, filtered those highlighted in green which has hidden characters inside.

[p45cal]

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:=xlPartin 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/unicode/char/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.

[Owl]

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.

[p45cal]

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.
27787

[Paul_Hossler]

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

27789

27790