How do I separate a specific set of numbers from a cell

[cecis]

Hello,
I want to extract the last set of numbers in a cell with multiple numbers and text in it.

60000 · Operating Expenses:62000 · T & E:62320 · Meals & Ent - External

60000 · Operating Expenses:62000 · T & E:62400 · Registration Fees

60000 · Operating Expenses:62000 · T & E:62500 · Other T&E

60000 · Operating Expenses:63000 · Consulting

60000 · Operating Expenses:64000 · Recruiting & Relocation:64100 · Recruiter Fees

60000 · Operating Expenses:65000 · General & Admin Fees:65010 · Legal Fees:65010-1 · Legal Fees- General

60000 · Operating Expenses:65000 · General & Admin Fees:65010 · Legal Fees:65010-2 · Legal Fees- Financing

60000 · Operating Expenses:65000 · General & Admin Fees:65020 · Insurance:65020-1 · General Insurance

<colgroup><col></colgroup><tbody>
</tbody>

The above shows a sample of rows in the spreadsheet. I want a formula that will move that set of numbers and the text to the right of that number in the following column. The last set is usually separated by the last ":".

 

[S O WXLS]

you can't "move" things with formulas, they can only look at another area and return a result - so you would either have to have formulas in each of the following columns checking for data, or you will need a VBA solution. If you could upload a sample of your data it would also be a big help.

 

[cecis]

I don't want to move the information. Just track that last 5 or 6 digit number in an adjacent column and the text following that number in the following column.

	A
1	31000 · Retained Earnings <tbody> </tbody> <tbody> </tbody>
2	40000 · Sales Revenue:41000 · Partners Revenue:41120 · Implementation Fees <tbody> </tbody>
3	40000 · Sales Revenue:42000 · Direct Merchant Revenues <tbody> </tbody>
4	40000 · Sales Revenue:43000 · Other Revenue <tbody> </tbody>
5	40000 · Sales Revenue:49000 · Sales, Returns & Credits:49200 · Refunds, Credits & Writeoffs <tbody> </tbody>
6	40000 · Sales Revenue:49000 · Sales, Returns & Credits:49400 · Consumer Loyalty Discounts <tbody> </tbody>
7	60000 · Operating Expenses:60100 · Advertising & Marketing:60130 · Advertising <tbody> </tbody>
8	60000 · Operating Expenses:60100 · Advertising & Marketing:60150-1 · Website <tbody> </tbody>
9	60000 · Operating Expenses:60100 · Advertising & Marketing:60160 · Other Marketing <tbody> </tbody>
10	60000 · Operating Expenses:60100 · Advertising & Marketing:60170 · Events & Sponsorship <tbody> </tbody>

<tbody>
</tbody>

I'm new to this forum so I'm not sure how to upload an excel file. But I recreated a sample of the spreadsheet have. The last number is the account number is this chart of accounts and the account name is the text that follows that number after the last. I want to have the account # in one column and the account name in the other.

you can't "move" things with formulas, they can only look at another area and return a result - so you would either have to have formulas in each of the following columns checking for data, or you will need a VBA solution. If you could upload a sample of your data it would also be a big help.

 

[mikerickson]

Try this.
Select your column of data
Use Text to columns with a colon delimiter
That should split your data into two columns
Then select the second column and use Text To Columns again, but this time use a fixed width of 5.
The end result should be your data in three columns, the middle column being the number that you want.

If that works, you can consider using the "do not import" option if you don't need the rest of your input data.

 

[cecis]

It doesn't work since the number I want is not always in the same spot. Sometimes it's at the beginning (if there aren't several numbers in the cell I want the only number to reflect).

Try this.
Select your column of data
Use Text to columns with a colon delimiter
That should split your data into two columns
Then select the second column and use Text To Columns again, but this time use a fixed width of 5.
The end result should be your data in three columns, the middle column being the number that you want.

If that works, you can consider using the "do not import" option if you don't need the rest of your input data.

 

[István Hirsch]

If the data are in column A, enter

in B1:

=TRIM(MID(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("ß",SUBSTITUTE(A1,":","ß",LEN(A1)-LEN(SUBSTITUTE(A1,":","")))))," ",REPT(" ",255)),1,15))

In C1:

=TRIM(MID(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("ß",SUBSTITUTE(A1,":","ß",LEN(A1)-LEN(SUBSTITUTE(A1,":","")))))," ",REPT(" ",255)),510,2550))

Excel Workbook
	A	B	C
1	60000 Operating Expenses:62000 T & E:62320 Meals & Ent - External	62320	Meals & Ent - External
2	60000 Operating Expenses:62000 T & E:62400 Registration Fees	62400	Registration Fees
3	60000 Operating Expenses:62000 T & E:62500 Other T&E	62500	Other T&E
4	60000 Operating Expenses:63000 Consulting	63000	Consulting
5	60000 Operating Expenses:64000 Recruiting & Relocation:64100 Recruiter Fees	64100	Recruiter Fees
6	60000 Operating Expenses:65000 General & Admin Fees:65010 Legal Fees:65010-1 Legal Fees- General	65010-1	Legal Fees- General
7	60000 Operating Expenses:65000 General & Admin Fees:65010 Legal Fees:65010-2 Legal Fees- Financing	65010-2	Legal Fees- Financing
8	60000 Operating Expenses:65000 General & Admin Fees:65020 Insurance:65020-1 General Insurance	65020-1	General Insurance
Sheet

 

[cecis]

Worked perfectly!
Thank you so much for your help!

If the data are in column A, enter

in B1:

=TRIM(MID(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("ß",SUBSTITUTE(A1,":","ß",LEN(A1)-LEN(SUBSTITUTE(A1,":","")))))," ",REPT(" ",255)),1,15))

In C1:

=TRIM(MID(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("ß",SUBSTITUTE(A1,":","ß",LEN(A1)-LEN(SUBSTITUTE(A1,":","")))))," ",REPT(" ",255)),510,2550))

*	A	B	C
1	60000 · Operating Expenses:62000 · T & E:62320 · Meals & Ent - External	62320	Meals & Ent - External
2	60000 · Operating Expenses:62000 · T & E:62400 · Registration Fees	62400	Registration Fees
3	60000 · Operating Expenses:62000 · T & E:62500 · Other T&E	62500	Other T&E
4	60000 · Operating Expenses:63000 · Consulting	63000	Consulting
5	60000 · Operating Expenses:64000 · Recruiting & Relocation:64100 · Recruiter Fees	64100	Recruiter Fees
6	60000 · Operating Expenses:65000 · General & Admin Fees:65010 · Legal Fees:65010-1 · Legal Fees- General	65010-1	Legal Fees- General
7	60000 · Operating Expenses:65000 · General & Admin Fees:65010 · Legal Fees:65010-2 · Legal Fees- Financing	65010-2	Legal Fees- Financing
8	60000 · Operating Expenses:65000 · General & Admin Fees:65020 · Insurance:65020-1 · General Insurance	65020-1	General Insurance

<colgroup><col style="width:30px; "><col style="width:324px;"><col style="width:92px;"><col style="width:186px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[István Hirsch]

You are welcome, thanks for the feedback.