Formatting Tiered Budget Sheet to combine parts of account numbers

[TheyCallMeNerd]

Hello

Recently I have started in a new position. I am an intermediate to advanced user of Excel and my VBA skills are minimal at best. So, I would like to avoid VBA if at all possible.

On to the question: I have a spreadsheet that is set up with account codes with the following format: ##-####-#### Department/Division/Account.

Department Division Account
Column A Column B Column C Desired Results(Column D)
00 0000 0000 00-0000-0000
1111 00-0000-1111
2222 00-0000-2222
1111 0000 00-1111-0000
1111 00-1111-1111
2222 00-1111-2222
3333 00-1111-3333
4444 00-1111-4444
01 0000 0000 01-0000-0000
1111 01-0000-1111
2222 01-0000-2222
1111 0000 01-1111-0000
1111 01-1111-1111
2222 01-1111-2222

I would like to create a formula that will concatenate all of these into the above listed format? Is this even possible? The information is a data dump from Report Server.

I do believe it would be nested IFS Statement. It is the lookup part that I am not sure which would be best. Index/Match or VLookup or XLookup. Any input would be greatly appreciated!

 

[Peter_SSs]

Welcome to the MrExcel board!

As you can see, your sample has lost all its formatting. For the future, investigate the following
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if this is what you want.

21 12 13.xlsm
	A	B	C	D
1	Dept	Div	Ac	Result
2	00	0000	0000	00-0000-0000
3			1111	00-0000-1111
4			2222	00-0000-2222
5		1111	0000	00-1111-0000
6			1111	00-1111-1111
7			2222	00-1111-2222
8			3333	00-1111-3333
9			4444	00-1111-4444
10	01	0000	0000	01-0000-0000
11			1111	01-0000-1111
12			2222	01-0000-2222
13		1111	0000	01-1111-0000
14			1111	01-1111-1111
15			2222	01-1111-2222
Combine

Cell Formulas
Range	Formula
D2:D15	D2	=TEXTJOIN("-",,LOOKUP("z",A$2:A2),LOOKUP("z",B$2:B2),C2)

 

[TheyCallMeNerd]

Welcome to the MrExcel board!

As you can see, your sample has lost all its formatting. For the future, investigate the following
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if this is what you want.

21 12 13.xlsm
	A	B	C	D
1	Dept	Div	Ac	Result
2	00	0000	0000	00-0000-0000
3			1111	00-0000-1111
4			2222	00-0000-2222
5		1111	0000	00-1111-0000
6			1111	00-1111-1111
7			2222	00-1111-2222
8			3333	00-1111-3333
9			4444	00-1111-4444
10	01	0000	0000	01-0000-0000
11			1111	01-0000-1111
12			2222	01-0000-2222
13		1111	0000	01-1111-0000
14			1111	01-1111-1111
15			2222	01-1111-2222
Combine

Cell Formulas
Range	Formula
D2:D15	D2	=TEXTJOIN("-",,LOOKUP("z",A$2:A2),LOOKUP("z",B$2:B2),C2)

Oh my goodness! With the holidays, I totally forgot to respond! Thank you for the response and the tidbits. I will definitely use it in the future.

 

[Peter_SSs]

Oh my goodness! With the holidays, I totally forgot to respond!

No problem. Hope it helped.