Converting account data in spreadsheet: single to dual column

Other Questions Google Sheets
1

Hey everyone! I’m working on an accounting spreadsheet and I need some help with a formula. Right now, I have all the account info in one column, but I want to split it into two columns based on some rules.

Here’s what I’m trying to do:

  • If the voucher number is 2, I need the account in the new column to be the opposite of what’s in the original column.
  • If the voucher number is 3, the new column should just show the first account.

I’ve set up column G to show the accounts that match column C. But I’m stuck on how to write a formula for column G that will give me the result I want (which I’ve shown as an example in column H).

Can anyone help me figure out the right formula for this? I’m not great with complex spreadsheet stuff, so any tips would be super helpful. Thanks in advance!

2

I’ve tackled a similar issue before, and here’s what worked for me:

For column G, try this formula:

=IF(B2=2, IF(FIND(“-”, C2)>0, RIGHT(C2, LEN(C2)-FIND(“-”, C2)), C2), IF(B2=3, LEFT(C2, FIND(“-”, C2)-1), C2))

This should handle the different scenarios you mentioned. It checks the voucher number in column B, then applies the appropriate logic.

For voucher 2, it flips the accounts if there’s a hyphen. For voucher 3, it grabs the first account. Otherwise, it keeps the original.

Test it out and let us know if you need any tweaks. Spreadsheet formulas can be finicky, but once you get them right, they’re incredibly powerful!

3

hey oscar, sounds like a tricky situation! have u tried using an IF statement? something like =IF(B2=2, opposite(C2), IF(B2=3, LEFT(C2, FIND(“-”, C2)-1), C2)) might work. not sure if thats exactly right but could be a starting point. good luck!

4

I’ve encountered similar challenges with accounting spreadsheets. Here’s a formula that might solve your problem:

=IF(B2=2, IF(ISERROR(FIND(“-”,C2)), C2, TRIM(RIGHT(SUBSTITUTE(C2,“-”,REPT(" “,LEN(C2))),LEN(C2)))), IF(B2=3, TRIM(LEFT(SUBSTITUTE(C2,”-“,REPT(” ",LEN(C2))),LEN(C2))), C2))

This formula first checks the voucher number in column B. For voucher 2, it reverses the accounts if there’s a hyphen. For voucher 3, it extracts the first account. In other cases, it keeps the original value.

Remember to adjust the column references if needed. Test it thoroughly with your data to ensure it works as expected across all scenarios.