Far fetched maybe?

[sythong]

Greetings

To me this looks impossible to be done but no harm
seeking input from the masters. What I would like to do
is split certain information in a single cell so that I can
have it laid out for pivot table purpose.

A B
Amount Others
1500 Rent 100,Water 200,Insurance 1200

Is there any formula (not VBA) where Cell B
can be regenerated to arrived at

Others Rent 100
Others Water 200
Others Insurance 1200

Thanks.

Thong

 

[Milo_Minderbinder]

probably, but it's so easy with VBA

On Error Resume Next
ActiveCell.Value = Replace(ActiveCell.Value, ",", Chr(10))

 

[Milo_Minderbinder]

try

="Others "&TEXT(SUBSTITUTE(B14,",",CHAR(10)&"Others "),"general")

you must format the cell as wrap text

 

[Scott Huish]

Perhaps:

="Others " & SUBSTITUTE(B1,",",CHAR(10)&"Others ")

 

[sythong]

Really very sorry for not putting my post clearly.

In one cell I have the following items (this is not
consistent i.e. there may be less or more items of
different nature)

Rent 100,Water 200,Insurance 1200

The originator of this worksheet have not input
it as my desired format so that I can do a pivot.

Rent 100
Water 200
Insurance 1200

Neither can I do a text to column since there is a variation
in the words as well as amount (an also it would take too long
a time)

Can the items in the one cell be formulated so that it
will become a matrix i.e. if there are 3 items seperated
by comma it will become 3 x 2. If there is only 1 item
it will be 1 x 1.

Hope it is not too confusing.

Thanks again


Thong

 

[goldfish]

Its still a bit confusing, but I think I've got it... Colo's HTML Maker is a great tool for conveying things like this (link at bottom of this page).

BUT If I understand you correctly, there should be a way.
I would first set up a matrix of cells that give you the starting locations of each line and word. So assuming A1 is

1500 Rent 100,Water 200,Insurance 1200

Then in A2 place 1 and A3 place

=Find(",",$A$1,A2)

Then drag that down.
Then In B2 place

=IF(Find(" ",$A$1,A2)<$A3,Find(" ",$A$1,A2),50000)

and drag this down and across.

Now in either a ways down or a ways to the right place formulas based off of these. You could use

=MID($A$1,A2,IF(B2=50000,$A3,B2)-A2)

And drag that down and across.

Anyway,
HTH, (I haven't tested it yet, as I'm on a linux machine right now, so you have to make either +1 or -1 adjustments to either the Starting or the Length of the Mid Statement)
~Gold Fish

 

[sythong]

goldfish

I can't seem to get it to work.

I will try to post again using Jeannie

Thanks


Thong

 

[goldfish]

Sorry, I made more than a few errors, I guess testing was needed.

In A2 Place:

=1

In A3 Place:

=IF(ISERROR(FIND(",",$A$1,A2+1)),LEN($A$1),FIND(",",$A$1,A2+1))+1

Then Drag This down. In B2 Place

=IF(ISERROR(FIND(" ",$A$1,A2+1)),LEN($A$1),IF(FIND(" ",$A$1,A2+1)>$A3,LEN($A$1),FIND(" ",$A$1,A2+1)))

Drag this down and across

Then in a different section place:

=MID($A$1,A2,MAX(0,IF(B2=LEN($A$1),$A3-1,B2)-A2))

And drag that down and across.

HTH,
~Gold Fish
(This one is tested and works!)