Splitting text into only two cells ?

[streets]

Hello. I'm an accountant. At where I work, for certain reports, we can download accounting data into excel that has the following in cell each, per row :

1000 Checking
1100 Money Market - Endowment
1106 Money Market - Oper/Temp Restr
1107 Money Market - Board Reserve/GALA Festival Designated
1200 Accounts Receivable
1299 Doubtful accounts allowance
1300 Pledges Receivable
1305 Discounts - long-term pledges
1310 Doubtful pledges allowance
1320 Grants receivable
1345 Discounts - long-term grants
1400 Inventory - CDs, Videos
1500 Prepaid expenses
1595 Deposits
1705 Land - operating
1710 Buildings - operating
1720 Leasehold improvements
1730 Furniture, fixtures & equipment
1735 Software
1750 Vehicles
1790 Accumulated depreciation
1800 Other long-term assets
2000 Accounts payable
2050 Credit card
2070 Consignment Sales Payable
2071 Gifts Payable
2075 Sales tax payable
2080 Misc Payroll Liabilities
2100 Accrued liabilities
2200 Deferred revenue
2310 Notes payable current - Board
2320 Line of credit payable
2330 Notes payable current - other
2400 Other current liabilities
3000 Unrestricted net assets
3100 Temporarily restricted net assets
3200 Permanently restricted net assets

I need the chart of account numbers and the text separated, using only two cells per line to do that. How to do that?

For example, immediately after the download of the excel file, let's say that, by default, the "1107 Money Market - Board Reserve/GALA Festival Designated" line occupies excel cell A4. After the cell split, the desired result is that just the number, "1107", occupies cell A4 and and all of the text piece, the "Money Market - Board Reserve/GALA Festival Designated", is in cell B4. How to do that?

I've searched the internet, before posting here. There is endless "Text to Column" instruction, found on so many innumerable websites. There is online an endless list "Text to Column" instruction. The flaw in using "Text to Column" function, is that for the text piece of the cell, this "Text to Column" excel function separates each word and dash symbol into its own cell, ends-up spanning over 7 cells. This is un-acceptable, for what I prepare. I need the text "Money Market - Board Reserve/GALA Festival Designated" to be only occupying one cell. And so on, for all of the other accounts, listed above.

 

[Rick Rothstein]

Since all your account numbers are the same length, select the column and then open the Text To Columns dialog box (Data tab, Data Tools panel)... select "Fixed Width" and click the "Next" button... a single line separating the account numbers from the text should be all you see in the example table (if not, double click the ones you do NOT want)... click the "Finish" button.

 

[streets]

Rick Rothstein, that perfectly worked. Thank you.

Just in case I come across account numbers that are more than 4 characters in length, my next question is, how to do the split if all my account numbers are not the same length ? For example:

1000 Checking
1100 Money Market - Endowment
1106.2 Money Market - Oper/Temp Restr
1107g1 Money Market - Board Reserve/GALA Festival Designated
120078 Accounts Receivable
1299 Doubtful accounts allowance

 

[Cubist]

How about this?

Book3
	K	L	M
12	1000 Checking	1000	Checking
13	1100 Money Market - Endowment	1100	Money Market - Endowment
14	1106.2 Money Market - Oper/Temp Restr	1106.2	Money Market - Oper/Temp Restr
15	1107g1 Money Market - Board Reserve/GALA Festival Designated	1107g1	Money Market - Board Reserve/GALA Festival Designated
16	120078 Accounts Receivable	120078	Accounts Receivable
17	1299 Doubtful accounts allowance	1299	Doubtful accounts allowance
Sheet13

Cell Formulas
Range	Formula
L12:M17	L12	=TEXTSPLIT(SUBSTITUTE(K12," ","*",1),"*")
Dynamic array formulas.

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option, just need these formulas in the top cell of each column.

24 03 24.xlsm
	A	B	C
1	1000 Checking	1000	Checking
2	1100 Money Market - Endowment	1100	Money Market - Endowment
3	1106 Money Market - Oper/Temp Restr	1106	Money Market - Oper/Temp Restr
4	1107 Money Market - Board Reserve/GALA Festival Designated	1107	Money Market - Board Reserve/GALA Festival Designated
5	1200 Accounts Receivable	1200	Accounts Receivable
6	1299 Doubtful accounts allowance	1299	Doubtful accounts allowance
7	1300 Pledges Receivable	1300	Pledges Receivable
8	130562 Discounts - long-term pledges	130562	Discounts - long-term pledges
9	1310 Doubtful pledges allowance	1310	Doubtful pledges allowance
10	1320 Grants receivable	1320	Grants receivable
11	1345 Discounts - long-term grants	1345	Discounts - long-term grants
12	1400 Inventory - CDs, Videos	1400	Inventory - CDs, Videos
13	1500 Prepaid expenses	1500	Prepaid expenses
14	1595 Deposits	1595	Deposits
15	17 Land - operating	17	Land - operating
16	1710 Buildings - operating	1710	Buildings - operating
17
Split

Cell Formulas
Range	Formula
B1:B16	B1	=TEXTBEFORE(A1:A16," ")
C1:C16	C1	=TEXTAFTER(A1:A16," ")
Dynamic array formulas.