Long string, varying length, extracting a value


Hi - Hope you can help. I've got long strings of data of varying lengths in a single column. I want to be able to extract the value which is always four semi-colons from the end irrespective of the length of the data in the cells. In the shortened example below, I am trying to extract the value '85.60'. I'm not very good, but have tried various options involving substitution, find, mid, etc, but am sure there must be a more elegant formula. Any help would be massively appreciated!


Many thanks,





If you don't want to use a formula you can use Text to Columns.

Select the cell > go to the Data tab > click Text to Columns > Choose Delimited and hit Next > put a check mark next to Other and then type a semi-colon in the input box next to it and make sure no other option is checked > hit FInish.

But, if you already have a formula that works, go with it. There is no elegant formula solution to this in Excel.


For a macro solution, use this:

Sub get_value()

Dim value_array() As String

last_row = Range("A" & Rows.Count).End(xlUp).Row

For row_num = 1 To last_row

    text_value = Cells(row_num, 1).Value
    value_array = Split(text_value, ";")

    For i = LBound(value_array) To UBound(value_array)

        If i = (UBound(value_array) - 4) Then

            Cells(row_num, 2).Value = value_array(i)

        End If


Next row_num
End Sub

It assumes that your values are in column A.



Many thanks for the reply, Don. Some clarification: I can't use 'text to columns' because the data in each cell is of varying length. This gives me far too many columns to deal with to try and obtain the final data value which is always between the fourth and fifth semi colons from the right end of the string of data in each cell irrespective of the length of the data in the cell. I've got nearly 700 rows. For example the data in one cell is:   31/03/2009;173.00;-1,944.00;-318.70;0.0;0.0;0%;3.92;31/03/2010;169.00;159.00;17.20;0.0;0.0;0%;3.00;31/03/2011;276.00;189.00;19.60;0.0;0.0;0%;3.60;31/03/2012;169.00;-777.00;-82.80;0.0;0.0;0%;8.10;31/03/2013;108.00;363.00;38.30;0.0;0.0;0%;8.10;31/03/2014;207.00;520.00;54.80;0.0;0.0;0%;16.00;31/03/2015;134.00;702.00;73.90;0.0;0.0;0%;20.00;31/03/2016;103.00;819.00;85.60;0.0;0.0;0%;22.00   And in another cell:   30/06/2011;3.97;3.50;7.77;0.0;0.0;0%;6.00;30/06/2012;9.04;8.35;8.15;0.0;0.0;0%;6.40;30/06/2013;10.34;9.51;8.68;0.0;0.0;0%;6.70;30/06/2014;11.25;10.16;9.28;0.0;0.0;0%;7.17;30/06/2015;13.30;12.10;11.05;0.0;0.0;0%;8.00;30/06/2016;13.89;12.67;11.56;0.0;0.0;0%;9.00   In each case, I'm trying for a formula for extracting the value between fourth and fifth semi-colon from right hand end into a new cell in a new column on the same row. So in 1st eg, it would be the value 85.60 and in 2nd eg it would be 11.56.   I haven't been able to work up any formula that will do this for me, elegant or not.   Many thanks for any further thoughts, Ade.
larj Aug 6, '16 at 1:07 pm
Answer updated. Try the macro and see how it works for you.
don (rep: 1521) Aug 8, '16 at 4:50 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login