Relevant answer
option for reference D10=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT($D$9,FIND("^",SUBSTITUTE($D$9&CHAR(10),CHAR(10),"^",ROW(A1)))-1),CHAR(10),REPT(" ",50)),50)),"")
Relevant answer
try: with MS365 =TEXTBEFORE(A1:A3,".",-1)
try: with Excel 2013+ versions
=SUBSTITUTE(A1,"."&FILTERXML("","//b[last()]"),"")
Relevant answer
=TEXTBEFORE(A1,".",-1) =SUBSTITUTE(A1,"."&FILTERXML("","//b[last()]"),"")
Relevant answer
Try: =FILTERXML("","//b[last()-1]")
MS365 =@TAKE(TEXTSPLIT(E9,", "),-2)
Relevant answer
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
Relevant answer
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
Relevant answer
What version of excel do you have?????????? Microsoft 365 has some good functions to do this job??????????????????????
Relevant answer
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,""))
Relevant answer
Relevant answer
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10)) The above should do it to pull the last word / city out.
Relevant answer
=IFERROR(REPLACE(FILTERXML("";"//b["&COLUMN()-1&"]");FIND(".";FILTERXML("";"//b["&COLUMN()-1&"]");1);100;"");"")
Relevant answer
=SUM(--TEXTSPLIT(A1,"+"))
=SUMPRODUCT(FILTERXML(""&SUBSTITUTE(A1,"+","")&"
Relevant answer
Jan Bax please refer image below for a solution
• Formula used in cell A10
=TEXTSPLIT(TEXTJOIN("-",,REPT(B1:B4&"|",A1:A4)),"-","|",1)
Relevant answer
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
Relevant answer
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&" "))