Forum Discussion
Solved
Finding and replacing a number with a table
I am using Office 365 on a windows 11 PC. If it is possible how do I find and replace a number with a table of numbers? For example, I download a CSV file of bank transactions for a business chec...
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.
Not entirely sure what you want to do, but I'm sure it is doable.
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.
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.
