Here is a more efficient way as it eliminates 3 out of 4 sort operators found in the ROW_NUMBER solution's execution plan.
😎

USE...

No need for a cursor here, simply filter the set first and then unpivot it.
😎

Here is an example

USE TEEST;
GO
SET NOCOUNT...

Apart from the OP's problem already being solved with no relation to the majority of the subsequent thread, I think there is time for someone to write up the ever...

On the float vs numeric, for the purpose of calculating geographical distances, float is the correct data type IF the correct calculation methods are used, which by the way, expect...

An alternative to Jeff's fine solution
😎

USE TEEST;
GO
SET NOCOUNT ON;

SELECT
  t3.data                     AS ORIGINAL_INPUT
 ,PARSENAME(REPLACE(PARSENAME(REPLACE(t3.data,'^','.'),1),'|','.'),2) AS DESIRED_VALUE
 FROM (VALUES
   ('Abcdefghi^4|2344')