Split first part using Evaluate

[YasserKhalil]

Hello everyone

There are values in column B that looks like that
Hans_Yasser

The values are separated by underscore. How can I get the first part of the values into column A using Evaluate?
This is my try but I got all the results the same from the B1 only

Code: Select all

Sub Test()
    Dim rng As Range

    Set rng = Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
    rng.Offset(, -1).Value = Evaluate("LEFT(" & rng.Address(0, 0) & ",SEARCH(""_""," & rng.Address(0, 0) & ",1)-1)")
End Sub

[HansV]

I ran your code and it did produce the correct result for every cell:

S3010.png

By the way, I'd use FIND instead of SEARCH. Since case-sensitivity doesn't matter here, FIND will be slightly faster.

[YasserKhalil]

That's weird. I got Hans for all the results
Can you have a look at the sample please?

[HansV]

This is what I get. I'm using Excel 2019.

S3012.png

[YasserKhalil]

That's very weird. Why using different versions results in different results?
I am using 365 now and all I got in A1:A3 is the string "Hans"
Generally
Can you provide me with a solution that doesn't use loops as Evaluate?

[HansV]

All other methods I can think of involve looping.

[Doc.AElstein]

Hi Yasser, how are you?

Why using different versions results in different results?

Microsoft recently introduced the new Dynamic Arrays to some users and some versions: this was a very bad idea in my opinion. It will frequently “throw a spanner in the works” as a result of different version compatibility. ( http://www.excelfox.com/forum/showthrea ... -CSE-Entry" onclick="window.open(this.href);return false; )
Evaluate Range things like your code line, frequently require a “coercion” to get the array results out which are available. The changes introduced by the new Dynamic Array may, I guess, effect how these things work. I am not sure. I only have up to Office 2010.

In Excel 2003, 2007 and 2010, I also get the same results of Hans in all rows. This is what I would usually expect.
Hans.JPG : https://imgur.com/Sq3P6dT" onclick="window.open(this.href);return false;

Hans.JPG

There are a few tricks that can coerce a code line like yours into giving back the array of results
This one is the one I most use
If({1}, __________ )

Applied to your code line it then gives me all three results
HansYasserSalem.JPG : https://imgur.com/eM4nzbr" onclick="window.open(this.href);return false;

HansYasserSalem.JPG

Code: Select all

Sub Test()
Dim rng As Range: Set rng = Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
 rng.Offset(, -1).Value = Evaluate("LEFT(" & rng.Address(0, 0) & ",SEARCH(""_""," & rng.Address(0, 0) & ",1)-1)")
 ' If({1}, _____ )
 rng.Offset(, -1).Value = Evaluate("If({1},LEFT(" & rng.Address(0, 0) & ",SEARCH(""_""," & rng.Address(0, 0) & ",1)-1))")
End Sub

Alan


https://www.mrexcel.com/board/threads/r ... st-5401671" onclick="window.open(this.href);return false;

[YasserKhalil]

Thanks a lot Mr. Alan for this awesome trick. I have used this trick before but it seems I forgot it at all
Thank you very much.

[Doc.AElstein]

..... a solution that doesn't use loops as Evaluate?

You can copy the range to the clipboard, do some manipulation of that complete range string held in the clipboard, as we have done before, ( http://www.eileenslounge.com/viewtopic. ... 95#p242941" onclick="window.open(this.href);return false; , http://www.eileenslounge.com/viewtopic. ... 89#p243731" onclick="window.open(this.href);return false; , http://www.eileenslounge.com/viewtopic. ... 38#p247681" onclick="window.open(this.href);return false; , ) , then put the manipulated string in the clipboard and paste it out.

This macro pair seems to work. ( You only need to run the first one, - the second is set off after the first finishes). But I don’t understand too well, (yet), all what is going on…

Code: Select all

 Option Explicit
Dim Globy As String
Sub DoItOverTheWindowsClipboard()
 ActiveSheet.Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row).Copy
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Dim strBack As String
 objDataObject.GetFromClipboard: Let strBack = objDataObject.GetText() ' From Clipboard   "Hans" & "_" & "Test" & vbCr & vbLf & "Yasser" & "_" & "Khalil" & vbCr & vbLf & "Salem" & "_" & "KH" & vbCr & vbLf
Dim regEx As Object: Set regEx = CreateObject("vbscript.regexp")
 regEx.Pattern = "_" & ".+": regEx.Global = True
 Globy = regEx.Replace(strBack, "")  '    Globy = "Hans" & vbLf & "Yasser" & vbLf & "Salem" & vbLf
 Application.OnTime EarliestTime:=Now(), Procedure:="ClipOut"
End Sub
' Stoping the sub seems to cut the link to the clipboard. Without it you always get pasted from  B1:B3  , regardless of the  .PutInClipboard
Sub Clipout()
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
 objDataObject.SetText Globy
 objDataObject.PutInClipboard
 ActiveSheet.Paste Destination:=Range("A1")
End Sub

I think the range evaluate single code line solution is best.
Range evaluate solutions I use a lot. They seem to be very fast.

[YasserKhalil]

Thanks a lot Mr. Alan for this illustration.
Best Regards

[Doc.AElstein]

Hello Yasser,
I have a question for you please..

I am using 365

Are you on the Microsoft "Office insider" program?
Are you an "Office insider"?

If you do not understand my question, then the answer is probably no.
( The Microsoft insider program , allows users who are "Office insider" 's to receive new functions before they are generally available to all users )

Thanks
Alan

[YasserKhalil]

No I am not Office Insider ..

[Doc.AElstein]

Thanks for the reply Yasser
If anyone passing this thread is on the insider program and has Office 365 with Dynamic arrays, then it might be interesting to see what results they get for your example….

Alan

[rory]

Out of interest, can you guys tell me what this line of code returns on your various versions:

Code: Select all

Msgbox Typename(Evaluate("ROW(A1)"))

I'm curious to see who gets an array and who doesn't.

[HansV]

Excel 2019, version 1912 (build 12325.20344 Click-to-Run).

Result: Variant()

[Doc.AElstein]

2003, 2007 and 2010 all is giving
Variant()
at my end...

[rory]

Interesting, I was expecting Hans' result with 2019, but not Alan's.

[Doc.AElstein]

I think I would have mostly expected that. I don’t think I ever noticed a Row() or Column()type things ever “not work” to return an array inside Evaluate
As I understood it, we often chuck a Row() or Column() somewhere in side Evaluate to force an array to come out…
At least that is what I often have done, and so has the wife.

[rory]

How about:

Code: Select all

Msgbox Typename(Evaluate("MOD(ROW(A1),1)"))

[Doc.AElstein]

2003 2007 and 2010 all is giving
Double

_._____________________________________

( these all give Variant(), but I expect we all expect that...
MsgBox TypeName(Evaluate("If({1},MOD(ROW(A1),1))"))
MsgBox TypeName(Evaluate("If(Row(),MOD(ROW(A1),1))")
MsgBox TypeName(Evaluate("Index(MOD(ROW(A1),1),)"))
)