#### ride2esc

##### New Member
Hello everyone
I am trying to make multi-calculation at once in excel, and firs I want to know if possible, and if yes..then how

From another tool I get some data which look like this:

Bot, [03.02.21 20:48]
Sell order executed at Bittrex
43.14492517 ZRX @ 0.73 USDT
Total: 31.49 USDT
Fees paid: 0.06 USDT
Average rate: 0.70 USDT
Break Even Price: 0.70 USDT
Profit/Loss of this trade: 1.50 USDT (5.28%)
Overall Profit/Loss: 6.19 USDT

Bot, [03.02.21 21:05]
Sell order executed at Binance
15634 HOT @ 0.00 USDT
Total: 12.99 USDT
Fees paid: 0.01 USDT
Average rate: 0.00 USDT
Break Even Price: 0.00 USDT
Profit/Loss of this trade: 0.41 USDT (3.38%)
Overall Profit/Loss: 3.85 USDT

And so on..
I want to extract in another sheet or in another column the following data:

 Date Market (bittrex, binance, etc) Profit/loss Overall profit/loss 03.02.21 20:48 Bittrex 1.50 3.85

After this is done, I want to do some calculations. Sums, and other, but those I know how.
So, I can copy and paste from the other program the data, then paste it in excel then the report.

Thank you

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### jtakw

##### Well-known Member
Hi,

Is each "set" all in 1 cell?
If so, does it contain carriage returns?
You only showed 2 samples, are All your data in the Exact same format?

Assuming all Yes to the above, try these:

Book3.xlsx
ABCD
1Bot, [03.02.21 20:48] Sell order executed at Bittrex 43.14492517 ZRX @ 0.73 3USDT Total: 31.49 4USDT Fees paid: 0.06 5USDT Average rate: 0.70 6USDT Break Even Price: 0.70 7USDT Profit/Loss of this trade: 1.50 8USDT (5.28%) Overall Profit/Loss: 6.19 9USDT
2Bot, [03.02.21 21:05] Sell order executed at Binance 15634 HOT @ 0.00 USDT Total: 12.99 USDT Fees paid: 0.01 USDT Average rate: 0.00 USDT Break Even Price: 0.00 USDT Profit/Loss of this trade: 0.41 USDT (3.38%) Overall Profit/Loss: 3.85 USDT
3
4
5DateMarket (bittrex, binance, etc)Profit/lossOverall profit/loss
603.02.21 20:48Bittrex1.56.19
703.02.21 21:05Binance0.413.85
Sheet729
Cell Formulas
RangeFormula
A6:A7A6=IFERROR(MID(REPLACE(A1,FIND("]",A1),255,""),FIND("[",A1)+1,99),"")
B6:B7B6=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A1,CHAR(10),REPT(" ",99),2),99))," ",REPT(" ",99)),99))
C6:C7C6=IFERROR(LEFT(SUBSTITUTE(TRIM(MID(A1,SEARCH(C\$5,A1)+26,255))," ",REPT(" ",99)),99)+0,"")
D6:D7D6=IFERROR(LEFT(SUBSTITUTE(TRIM(MID(A1,SEARCH(D\$5,A1)+20,255))," ",REPT(" ",99)),99)+0,"")

#### ride2esc

##### New Member
I think my example is bad
I am pasting the info from a telegram channel.

Each raw has it's own cell actually.

I can manually delete and paste new data for calculations.
Can this be done?
In your example the A column is taken, and I can not paste.
I have looots of data to paste in my clipboard, but as I said, each cell has it's own value.
lile in my prinscreen attached.
If excel can not do it, maybe you have other workaround/ideas to colect data from Telegram bot and to calculate that.

#### Attachments

• telegram paste.png
43.3 KB · Views: 10

##### Well-known Member
Hi
If VBA's Ok with you Try
VBA Code:
``````Sub test()
Dim a As Variant
Dim ar As Range
For Each ar In Columns("A:A").SpecialCells(xlCellTypeConstants, 2).Areas
a = ar
a(1, 1) = Split(Split(a(1, 1), "[")(1), "]")(0)
a(2, 1) = Split(a(2, 1), "at")(1)
a(3, 1) = Trim(Split(Split(Split(a(8, 1), ":")(1), " ")(0), "USDT")(0)) * 1
a(4, 1) = Trim(Split(Split(Split(a(9, 1), ":")(1), " ")(0), "USDT")(0)) * 1
Cells(Cells(Rows.Count, 3).End(xlUp).Row + 1, 3).Resize(, 4) = Application.Transpose(a)
Next
End Sub``````

#### ride2esc

##### New Member

Hey
For me anything that works is ok.
Please..tell me step by step how to use this VBA code.

What I did:
Alt+F11. Then pasted this code in Sheet1. Then run.
Then error - compule error invalid outside procedure.

##### Well-known Member
Alt+F11.
Insert Module
Past the code
The sheet with your data should be active
Run

#### ride2esc

##### New Member

See my attachment error.
What I am doing wrong?

#### Attachments

• error.png
114.3 KB · Views: 6

##### Well-known Member
Try this version
VBA Code:
``````Sub test()
Dim a As Variant
Dim ar As Range
Range(Cells(1, 3), Cells(1, 7)) = Array("Date", "Market (bittrex, binance, etc)", "Profit/loss", "Overall profit/loss")
For Each ar In Columns("A:A").SpecialCells(xlCellTypeConstants, 2).Areas
a = ar
a(1, 1) = Split(Split(a(1, 1), "[")(1), "]")(0)
a(2, 1) = Split(a(2, 1), "at")(1)
a(3, 1) = Split(Split(Split(a(8, 1), ":")(1), " ")(0), "USDT")(0) * 1
a(4, 1) = Split(Split(Split(a(9, 1), ":")(1), " ")(0), "USDT")(0) * 1
Cells(Cells(Rows.Count, 3).End(xlUp).Row + 1, 3).Resize(, 4) = Application.Transpose(a)
Next
End Sub``````

Last edited:

#### ride2esc

##### New Member
Same error message...
If I press debug - this line get's highlighted in reallow

a(3, 1) = Split(Split(Split(a(8, 1), ":")(1), " ")(0), "USDT")(0) * 1

#### Fluff

##### MrExcel MVP, Moderator
Try it like
VBA Code:
``````    For Each ar In Columns("A:A").SpecialCells(xlCellTypeConstants, 2).Areas
a = ar
a(1, 1) = Split(Split(a(1, 1), "[")(1), "]")(0)
a(2, 1) = Split(a(2, 1), "at")(1)
a(3, 1) = Split(Split(a(8, 1), ": ")(1), "USDT")(0) * 1
a(4, 1) = Split(Split(a(9, 1), ": ")(1), " ")(0) * 1
Cells(Cells(Rows.Count, 3).End(xlUp).Row + 1, 3).Resize(, 4) = Application.Transpose(a)
Next``````

Replies
3
Views
166

1,141,715
Messages
5,708,030
Members
421,540
Latest member
quocbinh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back