VBA IF(Left, IF(Right, IF(Mid in Col A and put associated value in Col I

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
654
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
I'm better with formulas than VBA so I'm looking for help. I get a .txt file from our company's bank on the first of each month and use Text to Columns to break it into appropriate, filterable columns and save as .XLSX. I can save as .XLSM, no problem. I also have to identify most (but not all) of the items in COL A and that identity is entered into the same row of COL I. The statement is several thousand pages long so it takes a lot of time to check each item in it. I created a Nested IF statement for Col I and it has worked for some of the identities but it so far has 27 Nested IFs and there are many more to add.

For Example:

COL A
Central Air Vendor #112233
Cable Service Provider
Rental XXXXXX9876 Unit
Central Air Vendor #445566
Central Air Vendor #778899
Rental XXXXXX6543 Unit
Cell Phone Provider

In COL I, I have the following formula that works fine: =If(Left(A2,5)="Cable","Acct 5555-55",If(A2,4)="Cell","Acct 5555-53",If(Right(A2,7)="#112233","AB123",If(Right(A2,7)="#445566","CD456",If(Right(A2,7)="#778899","EF789",If(Mid(A2,8,10)="XXXXXX9876","#951753",If(Mid(A2,8,10)="XXXXXX6543","#258654","")))))))

Obviously this is a very busy formula and I have so much more to add to it. I have shortened what I'm actually using and am also using dummy data but the gist is here. I know I can modify any code you can provide to get me started. If you have more questions or need further clarification, please reach out.

Any help someone can give me will be very appreciated. Thank you in advance.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,397
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Why not create a VLOOKUP table with the required matches??
 

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
654
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Why not create a VLOOKUP table with the required matches??
Oh, good grief! Now I feel stupid! That should work just fine. Thank you for the suggestion. If I fail miserably, I'll reach out again.

Thanks again!
 

Forum statistics

Threads
1,176,678
Messages
5,904,411
Members
435,090
Latest member
zero20

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top