Aug 28 2022 11:40 AM
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 checking account, some of the transactions are payments to loans, and I want to replace the single payment amount with a table that includes the principal and interest amounts. Is this doable, and if so, how?
Aug 28 2022 12:49 PM
Aug 28 2022 12:59 PM
@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.
Aug 28 2022 01:16 PM - edited Aug 28 2022 01:18 PM
SolutionIt 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.
Aug 28 2022 01:22 PM
Aug 28 2022 01:16 PM - edited Aug 28 2022 01:18 PM
SolutionIt 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.