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.