Extract numbers out of text strings

[Analystbycuriosity]

I need to analyze a list of 200 such excel entries daily.
These are Text strings in each cell.

A1: MANDAL FIN. CO. LTD SR- SD52 8.8 NCD 15JU27 FVRS10LAC|INE121A08OC|8.80|15-Jun-2027|100.250|100.2813|8.7360|500.00|-
A2: CORPORATION LTD. SR-I 7.69 LOA 16JN23 FVRS10LAC|INE029A0840|7.69|16-Jan-2023|100.00|100.0000|7.6841|1000.00|-
A3: ANAND FINANCE LIMITED SR-III BR NCD 24MY18 |INE093JCH6|0.00|24-May-2018|189.2240|189.2240|0.0000|25.00|-
A4: BANK LIMITED TRANCHE-1 SR-I 9 NCD 30DC21 FVRS50|INE092CK9|0.00|30-Dec-2021|103.2200|103.2200|8.0100|2.00|-
A5: HOUSING LIMITED SRIIICATIII&IV8.9NCD26SP21FV1000LOAUP25SP16|INE148IGF5|0.00|26-Sep-2021|101.1200|101.1200|8.5000|8.90|-
A6: RAILWAY CORPORATION LIMITED 6.88/7.38 BD 23MR23 FVRS1000 LOA UPTO 22MR13|INE0F07587|0.00|23-Mar-2023|103.2131|103.1691|6.1400|10000.00|-

I want below output for better analysis.
A3 has no coupon hence 0
A5 coupon does not have space on either sides

Coupon rate
8.8
7.69
0
8.9
6.88/7.38


all kinds of solution will be appreciated
Regards!

 

[Joe4]

A3 has no coupon hence 0

How can you tell?
What in that string indicates whether it has a coupon or not?

Also, should we determine which numbers to extract?
I was thinking the first set of numbers it finds, but A4 seems to buck that trend:

A4: BANK LIMITED TRANCHE-1 SR-I 9 NCD 30DC21 FVRS50|INE092CK9|0.00|30-Dec-2021|103.2200|103.2200|8.0100|2.00|-

I think we need you to define the rules that we need to follow in order to identify the coupon codes.
We could try "guessing", but there is a good chance that we may guess wrong since we do not have all the information, and you have only provided a small sample size.

 

[Joe4]

OK, here is my best "guess". It works for all the examples you posted.
It looks for a numeric value containing a decimal point that occurs before the first pipe symbol "|".
It is a custom function that you will need to drop into VBA:

Function Coupon(myString As String) As String

    Dim myArray() As String
    Dim field1 As String
    Dim ln As Long
    Dim isNum As Boolean
    Dim hasDec As Boolean
    Dim i As Long
    Dim subStr As String
    
'   Set defaults
    isNum = False
    hasDec = False
    
'   Get first part of string (up to first "|") and get its length
    myArray = Split(myString, "|")
    field1 = myArray(0)
    ln = Len(field1)
    
'   Loop through field, if length is greater than 0
    If ln > 0 Then
        For i = 1 To ln
'           Check to see if character is numeric
            If IsNumeric(Mid(field1, i, 1)) Then
                isNum = True
                subStr = subStr & Mid(field1, i, 1)
            Else
                Select Case Mid(field1, i, 1)
                    Case "."
                        hasDec = True
                        subStr = subStr & Mid(field1, i, 1)
                    Case "/"
                        subStr = subStr & Mid(field1, i, 1)
                    Case Else
                        If isNum And hasDec Then
                            Coupon = subStr
                            Exit Function
                        Else
                            isNum = False
                            hasDec = False
                            subStr = ""
                        End If
                End Select
            End If
        Next i
    End If

End Function

Then you just use it like any other Excel function.
So if you wanted to return the coupon value for the entry in cell A1, you would just enter this formula in a blank cell on your sheet:
=Coupon(A1)

 

[Analystbycuriosity]

OK, here is my best "guess". It works for all the examples you posted.

Good Guess! Your function worked on 95% of 130 strings. THANKS Joe. You saved a lot of time.

What in that string indicates whether it has a coupon or not?

1. The bold text indicates the position. It is generally before words NCD BD LOA etc. Cant define.Your code worked on strings which had no number.
2. Most strings have space before and after the COUPON RATE. Exception A5

It looks for a numeric value containing a decimal point that occurs before the first pipe symbol "|".

I missed the A4 coupon in my output. My Bad.
Please also include numbers without decimal.Your code excludes strings similar to A4.It shows blank cell instead of "9"

I was thinking the first set of numbers it finds, but A4 seems to buck that trend:

A4: BANK LIMITED TRANCHE-1 SR-I 9 NCD 30DC21 FVRS50|INE092CK9|0.00|30-Dec-2021|103.2200|103.2200|8.0100|2.00|-

In many cases a number might occur before the coupon rate.
Till now i have seen strings having a number before coupon rate but with a hyphen "-" before it. Unlike the Coupon rate which has space before and after.

 

[Joe4]

I missed the A4 coupon in my output. My Bad.
Please also include numbers without decimal.Your code excludes strings similar to A4.It shows blank cell instead of "9"

I think this will create more problems than it solves.
Even if we take into account this:

Till now i have seen strings having a number before coupon rate but with a hyphen "-" before it.

and exclude numbers preceded by a hyphen, you have other numbers that will be returned that you don't want.

For example, A1 would then return 52 instead of 8.8, since the 52 appears first.
And then cell A3 would return 24 instead of nothing.

The issue here really is your data. You really don't have any set "rules" you can depend on (and lots of "exceptions" to the apparent rules).
Programming is very literal, it can only do what you tell it to. So if it is not possible to define clear rules that are always true, it won't be possible to program something that will work every time.
Usually, in these situations, the best you can do is to come up with a solution that works most of the time, and realize that there are exceptions that you are going to have to manually fix.

 

[mikerickson]

...1. The bold text indicates the position. ...

Is the bold text in your actual data or are you only using it for your examples?

If the bolding is in the cell in question,

Function ExtractBold(aCell As Range)
    Dim firstBold As Long, lenBold As Long
    Dim i As Long, flag As Boolean
    With aCell
        For i = 1 To Len(CStr(.Value))
            If .Characters(i, 1).Font.Bold Then
                If Not flag Then
                    firstBold = i
                    flag = True
                End If
            Else
                If flag Then Exit For
            End If
        Next i
        lenBold = i - firstBold
        If firstBold > 0 Then
            ExtractBold = .Characters(firstBold, lenBold).Text
        End If
    End With
End Function

 

[Analystbycuriosity]

Is the bold text in your actual data or are you only using it for your examples?

Hey!Thanks for the code Mike
Sorry i should have mentioned that it is only for presentation purpose.

 

[Analystbycuriosity]

I think this will createmore problems than it solves.

Agreed!
I am using custom function for the first time.
Problems i have faced
1. For a week i had to save function code everyday in insertmodule option in excel 2007and it gave the proper output.Next week even aftersaving the code daily the coupon function appears in the drop down menu butgives NAME! error. However in 2010 version daily saving of code is not required.