About Us

Mazzaropi

Quote:

Originally Posted by louiedelgrande

Hi, I have data in this format:

AAA111 Daycare Centre 40,066
ABC123 Daycare 18,231
A B C Charter Public School 8,090,691
Education House 8,088,750

-------------------

So I want to separate the data into two columns. The first column the name and the second the number value. It's a bit complicated because some of the company names have numbers in them.

Any help for how to split these cells? I was thinking text to columns, but there is no delimiter that works well. I'll bet there is a solution, but I'm not smart enough to figure it out. One of you smart people out there might be able to help I hope.

Thanks.

--------------------------------------------------------------------------

Dear louiedelgrande, Good Morning.

Suppose your data is at column A starting at A2

Then DO:

First Part of Text:
B2 -- =MID(A2,1,(FIND(" ",A2,(FIND(",",A2)-4))-1))

Second Part of Text:
C2 -- =RIGHT(A2,(LEN(A2)-FIND(" ",A2,(FIND(",",A2)-4))))

Try this one and tell me if it worked for you.

Feel free to ask anything about it.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Splitting Text from single cell in column across multiple Columns	Harold	Excel Worksheet Functions	3	March 14th 10 11:06 AM
Splitting column with text & numbers	craezer	Excel Discussion (Misc queries)	7	December 22nd 06 02:58 AM
splitting text within parenthese into new column	John	Excel Worksheet Functions	1	May 25th 05 04:34 PM
column splitting	holly	Excel Discussion (Misc queries)	2	March 1st 05 11:22 PM
Splitting text in one column into two (or more) columns.	RickyDee	Excel Worksheet Functions	4	December 7th 04 10:03 PM

Menu

About Us