Forum Discussion
Finding and replacing a number with a table
It is possible to use the & operator to combine the two amounts in one cell. Assume principal is in A1 and Interest is in B1.
Put this in the cell you want them to appear: = A1 & "," & B1
if formatting is needed: = TEXT(A1, "#,###.00") & "; " & TEXT(B1, "#,###.00")
IF labels are also needed: = "Principal=" & TEXT(A1, "#,###.00") & "; " & "Interest =" & TEXT(B1, "#,###.00")
NOTE! If you do this, and math you may want to perform on merging data in a cell becomes very difficult.
Step 1, load the CSV into Excel. Power query is the best way but there are other ways to do that with Excel.
Step 2 (of one option):
If you want the totals in the cell, use SUM or SUMIFS function to total the principal and interest amounts.
Step 2 (another option) If you want to place the transactions starting at the cell you want to replace, use FILTER()
If neither of these do what you want, I would need to know more before I can help.
Craig Hatmaker Hi Craig, I download the CSV file into Excel with no problem. The transaction is for $147. I want to record the fact that $100 of that amount is a principal payment and $47 is interest payment. Can I replace the single cell amount, $147, with the individual amounts of the two other cells, or should I create separate columns for principal and interest.
Thank you for your response.
It is possible to use the & operator to combine the two amounts in one cell. Assume principal is in A1 and Interest is in B1.
Put this in the cell you want them to appear: = A1 & "," & B1
if formatting is needed: = TEXT(A1, "#,###.00") & "; " & TEXT(B1, "#,###.00")
IF labels are also needed: = "Principal=" & TEXT(A1, "#,###.00") & "; " & "Interest =" & TEXT(B1, "#,###.00")
NOTE! If you do this, and math you may want to perform on merging data in a cell becomes very difficult.- Craig, thank you this is very helpful. I did not know about the & operator. I understand what you mean about the math on merging data in a cell becoming very difficult, but I don't think it will be an issue.
