vba help - Split data seperator Black Star

[Mallesh23]

Hi Team,

How to split the data, and Seperate name from Black Star.

Column A input data and Expected output is in Column B and c.

Book7
	A	B	C
1	Name	Expected Output	Count with special character
2	Sachin★★★	Sachin	3
3	Dhoni★★	Dhoni	2
4	Virat Kohli★	Virat Kohli	1
Sheet2

Thanks
mg

 

[mohadin]

B2=LEFT(A2,FIND("★",A2)-1)
C2==LEN(A2)-FIND("★",A2)+1

Or

B2=LEFT(A2,FIND("★",A2)-1)
C2=len(A2)-LEN(B2)

 

[Mallesh23]

Hi Mohadin,

Thanks for your help ,formula worked. But through vba I am not getting output.

.FormulaR1C1 = "=LEFT(RC[-1],FIND(""?"",RC[-1])-1)" this is recorded code.


★ star is getting replaced with ? in vba.



Thanks
mg

 

[Peter_SSs]

Try

Sub CountStars()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 2)
  For i = 1 To UBound(a)
    For j = 1 To Len(a(i, 1))
      If Mid(a(i, 1), j, 1) Like "[!A-Za-z ]" Then
        b(i, 1) = Left(a(i, 1), j - 1)
        b(i, 2) = Len(a(i, 1)) - j + 1
        Exit For
      End If
    Next j
  Next i
  Range("B2:C2").Resize(UBound(b)).Value = b
End Sub

 

[Mallesh23]

Hi Peter,

Very nice , liked your code, it worked. thanks ?


Thanks
mg

 

[Mallesh23]

Hi Peter,

Can you add comment for this regual Experession :=====> If Mid(a(i, 1), j, 1) Like "[!A-Za-z ]" Then , what it means

While debugging the code, if it found special charecter it went inside the if condition.




Thanks
mg

 

[Peter_SSs]

Hi Peter,

Very nice , liked your code, it worked. thanks ?


Thanks
mg

You're welcome.

Can you add comment for this regual Experession :=====> If Mid(a(i, 1), j, 1) Like "[!A-Za-z ]" Then , what it means

It isn't a regular expression as such - just the use of the 'Like' operator. The ! means any character not in the list that follows it.
Therefore that line of code finds the first character in the cell that is not a letter (upper or lower case) or a space & uses that position to both extract the text before that position and to count the remaining characters starting from that position.

To be robust the list probably needs some more characters added in case you have hyphenated names (eg Harris-Jones) or names with apostrophes (eg O'Connor)
[!A-Za-z '-]

While debugging the code, if it found special charecter it went inside the if condition.

If that is a further question them I'm not sure what you mean.

 

[Mallesh23]

Hi Peter,

Thanks for your help learned something new here, Got it. ?


Thanks
mg