how to convert column a question to column b (sorry xl2bb is not working )

[Kichan]

how to convert column a question to column b (sorry xl2bb is not working )

 

[etaf]

not following 100%
What version of Excel - Still 2010 ?
="{ question: "&LEFT(A2,SEARCH("=",A2,1)-1)&""", answer: """&RIGHT(A2,LEN(A2)-SEARCH("=",A2,1))&"""},"

Book1
	A	B
1
2	Question 1 = answer to Question 1	{ question: Question 1 ", answer: " answer to Question 1"},
3	Question 2 = answer to Question 2	{ question: Question 2 ", answer: " answer to Question 2"},
4
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	="{ question: "&LEFT(A2,SEARCH("=",A2,1)-1)&""", answer: """&RIGHT(A2,LEN(A2)-SEARCH("=",A2,1))&"""},"

 

[mumps]

Try:

Sub ConvertData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, Val As Variant, arr() As Variant, lRow As Long, x As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("A2:A" & lRow).Value
    For i = LBound(v) To UBound(v)
        Val = Split(v(i, 1), " = ")
        x = x + 1
        ReDim Preserve arr(1 To x)
        arr(i) = "{ " & """" & Val(0) & """" & ", answer: " & """" & Val(1) & """" & " },"
    Next i
    Range("A2").Resize(UBound(arr)) = Application.Transpose(arr)
    Application.ScreenUpdating = True
End Sub

 

[Kichan]

not following 100%
What version of Excel - Still 2010 ?
="{ question: "&LEFT(A2,SEARCH("=",A2,1)-1)&""", answer: """&RIGHT(A2,LEN(A2)-SEARCH("=",A2,1))&"""},"

Book1
	A	B
1
2	Question 1 = answer to Question 1	{ question: Question 1 ", answer: " answer to Question 1"},
3	Question 2 = answer to Question 2	{ question: Question 2 ", answer: " answer to Question 2"},
4
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	="{ question: "&LEFT(A2,SEARCH("=",A2,1)-1)&""", answer: """&RIGHT(A2,LEN(A2)-SEARCH("=",A2,1))&"""},"

Thank you very much

 

[Kichan]

Try:

Sub ConvertData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, Val As Variant, arr() As Variant, lRow As Long, x As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("A2:A" & lRow).Value
    For i = LBound(v) To UBound(v)
        Val = Split(v(i, 1), " = ")
        x = x + 1
        ReDim Preserve arr(1 To x)
        arr(i) = "{ " & """" & Val(0) & """" & ", answer: " & """" & Val(1) & """" & " },"
    Next i
    Range("A2").Resize(UBound(arr)) = Application.Transpose(arr)
    Application.ScreenUpdating = True
End Sub

thank you very much

 

[Kichan]

thank you very much

but there is some issue while running this code bro...

 

[mumps]

You are very welcome.

 

[Fuji]

If vba,

Sub test()
    Dim a, i&, x
    With Range("a2", Range("a" & Rows.Count).End(xlUp))
        a = .Value
        ReDim Preserve a(1 To UBound(a, 1), 1 To 2)
        For i = 1 To UBound(a, 1)
            If a(i, 1) Like "*=*" Then
                x = Application.Trim(Split(a(i, 1), "="))
                a(i, 1) = Join(Array("{ question: ", x(1), " "" ,answer: ", x(2), """},"), "")
            End If
        Next
        .Columns(2) = a
    End With
End Sub