FILTERXML Function

Relevant answer

Learn with CUP 1 year ago

You can use a nested function, assuming your string is in cell A7, =transpose(value(textsplit(A7,”,”))) This formula will separate the numbers using the coma as divider, then the value function will convert text to number and lastly transpose will swap from row to column

3 Likes

Relevant answer

Andi Priyasdanto 1 year ago

Untuk kasus yang seperti ini, kalau datanya acak tapi itu itu aja... Maka bisa pakai tabel ke 3 (tabel referensi).. Jadi isi tabelnya untuk ngasih nilai referensi yang mudah di sumif... contohnya "Andi", "Angga" dan "Andi & Angga" dikasih satu nilai yang sama, misal 111. Baru nama yang lain nilainya beda/berurutan dan unik... Nantinya pakai sumif pakai nilai referensi

1 Like

Relevant answer

Scott Lu 1 year ago

This question is interesting, following is option for your reference (case sensitive) B11=IFERROR(INDEX($A$4:$A$8,TEXTJOIN("/",,IF((LEN($B$4:$B$8)=LEN(B11))ISNUMBER(FIND(" "&TRIM(LEFT(SUBSTITUTE($B$4:$B$8," ",REPT(" ",50)),50))&" "," "&B11&" ")) ISNUMBER(FIND(" "&TRIM(RIGHT(SUBSTITUTE($B$4:$B$8," ",REPT(" ",50)),50))&" "," "&B11&" "))*ISNUMBER(FIND(" "&TRIM(LEFT(SUBSTITUTE(B11," ",REPT(" ",50)),50))&" "," "&$B$4:$B$8&" "))*ISNUMBER(FIND(" "&TRIM(RIGHT(SUBSTITUTE(B11," ",REPT(" ",50)),50))&" "," "&$B$4:$B$8&" ")),ROW($B$4:$B$8)-3,""))/1),"/")

Formula in D11 is for visual verification reference (embedded into B11) D11=TEXTJOIN("/",,IF((LEN($B$4:$B$8)=LEN(B11))ISNUMBER(FIND(" "&TRIM(LEFT(SUBSTITUTE($B$4:$B$8," ",REPT(" ",50)),50))&" "," "&B11&" ")) ISNUMBER(FIND(" "&TRIM(RIGHT(SUBSTITUTE($B$4:$B$8," ",REPT(" ",50)),50))&" "," "&B11&" "))* ISNUMBER(FIND(" "&TRIM(LEFT(SUBSTITUTE(B11," ",REPT(" ",50)),50))&" "," "&$B$4:$B$8&" "))*ISNUMBER(FIND(" "&TRIM(RIGHT(SUBSTITUTE(B11," ",REPT(" ",50)),50))&" "," "&$B$4:$B$8&" ")),$B$4:$B$8,""))

1 Like

Relevant answer

Wittaya Chainim 1 year ago

=SUM(--TEXTSPLIT(A1,"+"))

=SUMPRODUCT(FILTERXML(""&SUBSTITUTE(A1,"+","")&"","//s"))

3 Likes

Relevant answer

Juninawan 1 year ago

50K should be not to formula try this any posibility of number divide by dot (.) or comma Sub test() Dim a, i As Long, b() As String, n As Long a = Range("A1:A" & Cells(Rows.Count, 1).End(3).Row).Value ReDim b(1 To UBound(a), 1 To 1) With CreateObject("VBscript.RegExp") .Global = True .Pattern = "\b\d+[.,]\d+\b" For i = 1 To UBound(a) If .test(a(i, 1)) Then Set m = .Execute(a(i, 1)) b(i, 1) = m(0).Value End If Next i End With [d1].Resize(UBound(a)).Value = b Erase b End Sub

1 Like

Relevant answer

Mayukh Bhattacharya 1 year ago

Scott Wilkinson try this way as shown in screenshot below to make it specific, try to make it explicit like i did

• Formula used in cell E2

=ISNUMBER(SEARCH(" "&D2&" "," "&$B$2:$B$6&" "))

Or,

• Formula used in cell F2

=ISNUMBER(FIND(" "&D2&" "," "&$B$2:$B$6&" "))

1 Like

Hot network questions