Need help parsing subtext in cell

[ediaz369]

I need to extract the amount in the red squares so that they can be used elsewhere. I am thinking I should start from the RIGHT, go two decimal points to the left, and extract what is to the left and right of that decimal and up to the blank spaces. Not sure how I need to go about it though. Any help would be much appreciated.

22183

[SamT]

How about you select several of those cells on the Worksheet, Copy them, then Paste them here, so we don't have to switch back and forth from Excel to Firefox typing them all in ourselves.

In the meantime, see if this gives you any ideas
Sub t()
Dim Cel As Range

For Each Cel In MyRange 'Define the range of cells you want
For i = 1 To 10 'Assumes max number spaces in a row is 10
'Get rid of all Multiple Spaces
Cel = Replace(Cel, " ", " ")
Next

msgBox Cel.Address 'Which cell are we operating on ATT
MsgBox CDble(Split(Cel, " ")(4)) 'Adjust "4" to suit
Next
End Sub

[Paul_Hossler]

They look like they're fixed position, so just use 'Text to Columns' on the Data tab

[ediaz369]

Sorry about that. While the digits will change it is always in this format. I'll go give your example a try. Thank you very much!



536893 536893 31.53 36.00 .00 88.51 4.78


537774 537774 .00 24.00 .00 24.00 .00


537788 537788 61.21 84.00 .00 185.95 .00


538541 538541 31.53 36.00 .00 88.51 .00


538888 138888 .00 36.00 .00 36.00 .00


538888 538888 70.10 60.00 .00 176.75 .00


538956 008956 9.21 48.00 .00 63.34 .00


539073 539073 .00 36.00 .00 36.00 .00


539088 000088 61.21 84.00 .00 185.95 .00


539088 009088 270.70 72.00 .00 522.85 .00


539088 039088 2.34 60.00 .00 63.90 .00


539088 139088 .00 60.00 .00 60.00 .00


539289 539289 146.06 108.00 .00 351.21 .31


539359 539359 61.21 84.00 .00 185.95 .00


539402 539402 61.21 84.00 .00 185.95 .00

[ediaz369]

They look like they're fixed position, so just use 'Text to Columns' on the Data tab

I'll give this a try also. Being separated by cells would make this much easier. Thanks!

[ediaz369]

Thanks. I ended up just using Trim to get rid of the spaces.