Extracting numerical data from a text string

[lewis1]

Hi everyone,

I have text data in the single cell in the following format.

[TABLE="width: 960"]
<tbody>[TR]
[TD="width: 960"]New Advice Requested 242.59 Target Bps, [ Quantity : 3,700, Bps : 0, Target Bps : 242.59 ][/TD]
[/TR]
</tbody>[/TABLE]

I would like to extract the 3 data points in the [ ] brackets into adjacent cells if possible.

1. Quantity
2. Bps
3. Target Bps.

Any help would be greatly appreciated.

Kind regards,
Lewis.

 

[HappyRaul]

@Lewis1

One easy and quick way of doing this by using Text to Columns. This can be found in "Data" tab in excel sheet

 

[lewis1]

Hi Happy Raul,

Agree, that was my first thought. What divider would you use? note there are comma separators in the numbers as well.

 

[HappyRaul]

@Lewis,

Select the Text in the excel sheet. Click on Text to Columns. There are two options Demilited and Fixed Width.

Please Select Fixed Width

Wherever you need a divider, double click - a arrow appears

If you don't need a divider, double click on the arrow, the arrow disappears.

Click Finish

 

[Comfy]

I need the practice so here's a Regex solution:

Excel 2010

	A	B
New Advice Requested 242.59 Target Bps, [ Quantity : 3,700, Bps : 0, Target Bps : 242.59 ]
Quantity
Bps
Target Bps

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3700[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]242.59[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=GetData($A$1,A4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Function GetData(RegStr As String, id As String) As Variant
Dim RegExp As Object
Dim allMatches As Object


Set RegExp = CreateObject("VBScript.RegExp")
With RegExp
        .IgnoreCase = True
        .Pattern = "\[ Quantity : (\d{1,3},\d{3}), Bps : (\d+), Target Bps : (\d+.?\d+) \]"
End With


Set allMatches = RegExp.Execute(RegStr)


Select Case LCase(id)
    Case "quantity"
        GetData = CLng(allMatches.Item(0).submatches.Item(0))
    Case "bps"
        GetData = CDbl(allMatches.Item(0).submatches.Item(1))
    Case "target bps"
        GetData = CDbl(allMatches.Item(0).submatches.Item(2))
    Case Else
        GetData = "No Match Found"
End Select


End Function

 

[István Hirsch]

Fill in B1, C1, D1 according to the table below, enter into B2 the formula, then drag it across to column D, and down:

=-LOOKUP(1,-MID($A2,FIND(B1&" : ",$A2)+LEN(B1)+3,{1,2,3,4,5,6,7,8,9,10}))

In the source text numbers separators have been changed, according to my (continental) system.

Excel Workbook
	A	B	C	D
1		Quantity	Bps	Target Bps
2	New Advice Requested 242.59 Target Bps, [ Quantity : 3.700, Bps : 0, Target Bps : 242,59 ]	3700	0	242,59
Sheet

 

[mick0005]

Hi everyone,

I have text data in the single cell in the following format.

[TABLE="width: 960"]
<tbody>[TR]
[TD="width: 960"]New Advice Requested 242.59 Target Bps, [ Quantity : 3,700, Bps : 0, Target Bps : 242.59 ][/TD]
[/TR]
</tbody>[/TABLE]

I would like to extract the 3 data points in the [ ] brackets into adjacent cells if possible.

1. Quantity
2. Bps
3. Target Bps.

Any help would be greatly appreciated.

Kind regards,
Lewis.

Hi Lewis,

I'm not sure this is the BEST way to do it, but it does work. I tested it with some strings that were longer values and it is still working. You should be sure to check it against a few more of your records to make sure.

Note that the formula is different for each of B1, C1, D1. All can be dragged down to subsequent rows.

Excel 2010

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]New Advice Requested 242.59 Target Bps, [ Quantity : 3,700, Bps : 0, Target Bps : 242.59 ][/TD]
[TD="align: center"]3,700[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]242.59[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]New Advice Requested 1235.62 Target Bps, [ Quantity : 15,200, Bps : 28, Target Bps : 1235.62 ][/TD]
[TD="align: center"]15,200[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]1235.62[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,": ",CHAR(1),1))+2,(FIND(CHAR(1),SUBSTITUTE(A1,", ",CHAR(1),2),2))-(FIND(CHAR(1),SUBSTITUTE(A1,": ",CHAR(1),1)))-2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,": ",CHAR(1),2))+2,(FIND(CHAR(1),SUBSTITUTE(A1,", ",CHAR(1),3),2))-(FIND(CHAR(1),SUBSTITUTE(A1,": ",CHAR(1),2)))-2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,": ",CHAR(1),3))+2,(FIND(CHAR(1),SUBSTITUTE(A1," ]",CHAR(1),1))-2)-(FIND(CHAR(1),SUBSTITUTE(A1,": ",CHAR(1),3))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[peshraw]

Problem 2

On January 1,2011 you were given the following post dated checks

[TABLE="align: left"]
<tbody>[TR]
[TD]Check no

[/TD]
[TD]
amount
[/TD]
[TD]
Data on check
[/TD]
[/TR]
[TR]
[TD]
0683
[/TD]
[TD]1950

[/TD]
[TD]30.may.2011

[/TD]
[/TR]
[TR]
[TD]0680
[/TD]
[TD]1300
[/TD]
[TD]28.feb.2011
[/TD]
[/TR]
[TR]
[TD]0690
[/TD]
[TD]2800
[/TD]
[TD]30.aug.2011
[/TD]
[/TR]
[TR]
[TD]0710
[/TD]
[TD]4000
[/TD]
[TD]30.nov.2011
[/TD]
[/TR]
</tbody>[/TABLE]


Assume that the monthly interest rate is % 2

a) calculate the present value of each check.
b) Suppose you got only one check with a data of 30 June 2011 .if this check has the same present value as the four checks above, what is its amount?

 

[mick0005]

Peshraw - this is not related to the OP's question at all. This looks like a question from your FIN 200 course... it doesn't look related to excel at all.

Problem 2

On January 1,2011 you were given the following post dated checks

[TABLE="align: left"]
<tbody>[TR]
[TD]Check no
[/TD]
[TD]amount[/TD]
[TD]Data on check[/TD]
[/TR]
[TR]
[TD]0683[/TD]
[TD]1950
[/TD]
[TD]30.may.2011
[/TD]
[/TR]
[TR]
[TD]0680[/TD]
[TD]1300[/TD]
[TD]28.feb.2011[/TD]
[/TR]
[TR]
[TD]0690[/TD]
[TD]2800[/TD]
[TD]30.aug.2011[/TD]
[/TR]
[TR]
[TD]0710[/TD]
[TD]4000[/TD]
[TD]30.nov.2011[/TD]
[/TR]
</tbody>[/TABLE]


Assume that the monthly interest rate is % 2

a) calculate the present value of each check.
b) Suppose you got only one check with a data of 30 June 2011 .if this check has the same present value as the four checks above, what is its amount?

 

[lewis1]

Thanks very much everyone! I will dry run these solutions tomorrow and let you know how it goes!

Tx again!