Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '*%text%*4' to data type int
The problem is that Element_Answer.Element_Answer column is referencing two distinct tables. The weird *%text%* prefix in some of the rows is apparently there just to tell whether the value references one table or the other. Nevertheless, the fact that two different tables are referenced from a single column is a violation of any normalisation rules.

So, I strongly believe that the best solution to this would include a redesign of the Element_Answer table, so as to split its Element_Answer column into two: one referencing Form_Element_Groups.sort_order and the other referencing Element_Answer_text.ID. Both columns would be numeric as they both are essentially just references to numeric IDs.

For the purposes of the scripts below, I am calling the column referencing feg.sort_order by the same name, sort_order, for consistency, and the other reference is called Element_Answer_text_ID. Feel free to replace the names with whatever you consider more appropriate.

So, first you create two new integer columns in Element_Answer:

ALTER TABLE dbo.Element_Answer
ADD sort_order int NULL
    CONSTRAINT FK_ElementAnswer_FEG
    FOREIGN KEY REFERENCES dbo.Form_Element_Groups (sort_order);

ALTER TABLE dbo.Element_Answer
ADD Element_Answer_text_ID int NULL
    CONSTRAINT FK_ElementAnswer_EAT
    FOREIGN KEY REFERENCES dbo.Element_Answer_text (ID);


Note: the above assumes that both feg.sort_order and eat.ID are either declared as PKs or have a UNIQUE constraint defined on them. Otherwise they cannot be referenced. You can still store references without a formally defined relationship between tables, but you cannot have guaranteed data integrity that way, so I recommend you make sure you can declare the new columns as foreign keys.

The next step would be to populate the new columns from the current ea.Element_Answer one. That can be done in one go with an UPDATE statement like this:

UPDATE
  dbo.ElementAnswer
SET
  sort_order = CASE LEFT(ElementAnswer, 8) WHEN '*%text%*' THEN NULL ELSE Element_Answer END,
  Element_Answer_text_ID = CASE LEFT(ElementAnswer, 8) WHEN '*%text%*' THEN SUBSTRING(Element_Answer, 9, 999999) END
;


Now that the references to different tables sit in different columns, you can drop the original:

ALTER TABLE dbo.Element_Answer
DROP COLUMN Element_Answer;


To retrieve data from Form_Element_Groups and Element_Answer_text, you can complete your query with outer joins to those tables in this manner:

SELECT
  ...
  feg.group_title,
  eat.Element_Answer_text,
  ...
FROM
  ...
  LEFT JOIN dbo.Form_Element_Groups feg ON ea.sort_order = feg.sort_order
  LEFT JOIN dbo.Element_Answer_text eat ON ea.Element_Answer_text_ID = eat.ID
;