Appendix Thread. 3 TEST COPY

[DocAElstein]

Some extra solutions for this Thread
https://excelfox.com/forum/showthrea...ontains-a-sign



Excel Solution
_____ Workbook: TextWith$InIt.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	5465 Apples$50 Twenty	=FIND("$",A1)	=LEFT(A1,B1)	=FIND(" ",C1)	=RIGHT(C1,LEN(C1)-D1)	=RIGHT(A1,LEN(A1)-B1)	=FIND(" ",F1)	=LEFT(F1,G1-1)	=E1&H1		=RIGHT(LEFT(A1,FIND("$",A1)),LEN(LEFT(A1,FIND("$",A1)))-FIND(" ",LEFT(A1,FIND("$",A1))))&LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("$",A1)))-1)	Apples$50	Apples$50
2	5687 Grapes$597 Three	=FIND("$",A2)	=LEFT(A2,B2)	=FIND(" ",C2)	=RIGHT(C2,LEN(C2)-D2)	=RIGHT(A2,LEN(A2)-B2)	=FIND(" ",F2)	=LEFT(F2,G2-1)	=E2&H2		=RIGHT(LEFT(A2,FIND("$",A2)),LEN(LEFT(A2,FIND("$",A2)))-FIND(" ",LEFT(A2,FIND("$",A2))))&LEFT(RIGHT(A2,LEN(A2)-FIND("$",A2)),FIND(" ",RIGHT(A2,LEN(A2)-FIND("$",A2)))-1)	Grapes$597	Grapes$597

Worksheet: Sheet2
_____ Workbook: TextWith$InIt.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	5465 Apples$50 Twenty	12	5465 Apples$	5	Apples$	50 Twenty	3	50	Apples$50		Apples$50	Apples$50	Apples$50
2	5687 Grapes$597 Three	12	5687 Grapes$	5	Grapes$	597 Three	4	597	Grapes$597		Grapes$597	Grapes$597	Grapes$597

Worksheet: Sheet2



Some VBA Solutions

Option Explicit
'    https://excelfox.com/forum/showthread.php/2738-PQ-make-new-column-by-extracting-a-word-from-a-cell-that-contains-a-sign     https://www.mrexcel.com/board/threads/power-query-make-new-column-by-extracting-a-word-from-a-cell-that-contains-a-sign.1165642/
Sub Frm1a() '
Dim vTemp As Variant ' =RIGHT(LEFT(A1,FIND(""$"",A1)),LEN(LEFT(A1,FIND(""$"",A1)))-FIND("" "",LEFT(A1,FIND(""$"",A1))))&LEFT(RIGHT(A1,LEN(A1)-FIND(""$"",A1)),FIND("" "",RIGHT(A1,LEN(A1)-FIND(""$"",A1)))-1)
 Let vTemp = Evaluate("=RIGHT(LEFT(A1,FIND(""$"",A1)),LEN(LEFT(A1,FIND(""$"",A1)))-FIND("" "",LEFT(A1,FIND(""$"",A1))))&LEFT(RIGHT(A1,LEN(A1)-FIND(""$"",A1)),FIND("" "",RIGHT(A1,LEN(A1)-FIND(""$"",A1)))-1)")
Debug.Print vTemp '  http://i.imgur.com/LARD8FB.jpg
Dim Rng As Range: Set Rng = Range("A1")
 Let vTemp = Evaluate("=RIGHT(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")),LEN(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")))-FIND("" "",LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & "))))&LEFT(RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")),FIND("" "",RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")))-1)")
                                'Set Rng = Range("A1:A2")
                                ' Let vTemp = Evaluate("=RIGHT(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")),LEN(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")))-FIND("" "",LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & "))))&LEFT(RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")),FIND("" "",RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")))-1)")
End Sub
Sub Frm1b()
Dim Rng As Range
    For Each Rng In Range("A1:A2")
     Let Rng.Offset(0, 11).Value = Evaluate("=RIGHT(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")),LEN(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")))-FIND("" "",LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & "))))&LEFT(RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")),FIND("" "",RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")))-1)")
    Next Rng
End Sub
Sub Frm2a()
Dim Rng As Range
    For Each Rng In Range("A1:A2")
    Dim vTemp As Variant, vTemp1 As Variant, vTemp2 As Variant
     Let vTemp = Split(Rng.Value, "$", -1, vbBinaryCompare)
     Let vTemp2 = Left(vTemp(1), InStr(1, vTemp(1), " ", vbBinaryCompare) - 1)
     Let vTemp1 = Split(vTemp(0), " ", -1, vbBinaryCompare)
     Let vTemp1 = vTemp1(UBound(vTemp1))
     Let vTemp = vTemp1 & "$" & vTemp2
    Next Rng
End Sub
Sub Frm2b()
Dim Rng As Range
    For Each Rng In Range("A1:A2")
    Dim vTemp As Variant ' , vTemp1 As Variant, vTemp2 As Variant
     Let vTemp = Split(Rng.Value, "$")
     'Let vTemp2 = Left(vTemp(1), InStr(vTemp(1), " ") - 1)
     'Let vTemp1 = Split(vTemp(0), " ")
     'Let vTemp1 = Split(vTemp(0), " ")(UBound(Split(vTemp(0), " ")))
     'Let vTemp = Split(vTemp(0), " ")(UBound(Split(vTemp(0), " "))) & "$" & Left(vTemp(1), InStr(vTemp(1), " ") - 1)
     Let Rng.Offset(0, 12).Value = Split(vTemp(0), " ")(UBound(Split(vTemp(0), " "))) & "$" & Left(vTemp(1), InStr(vTemp(1), " ") - 1)
    Next Rng
End Sub