Splitting text, how?

[fillefrans]

Hi everyone!

I have a speadsheet with one column containing product descriptions. I need to split the text in each cell so that it ends up in two separate cells on the same row. For example, say cell A1 contains the following:

A1: Acme Superproduct with 500 horsepower and three wheels

I want to split this so that I end up with:

A1: Acme Superproduct
B1: with 500 horsepower and three wheels

I know of the Convert Text to Columns option, as well as a couple of options using formulas, but the problem is that the text is never the same on each row, there is not a specific word count or character count where I want to split it. So I have to specify the split point manually for each cell, but rather than double clicking the cell to activate editing, selecting the text to move and cutting/pasting this to column B, I'm looking for a quicker way. Ideally, I would love to have some kind of split tool where I could just click the cell once in the place where I want to split and the text to the right of the cursor would move to column B. But I guess this doesn't exist...? Any other ideas?

Any help is very much appreciated! Thanks! :-)

[ramananhrm]

Are you looking for this result?

[mahaveersomani]

can you give me 2 more example so that i can understand properly. and can help you



Regards
CA Mahaveer Somani

[XOR LX]

@ramananhrm

I'm curious - have I missed some of the original post or do you have extra information which tells you that every string is to be split precisely after the second word?

Regards

[mahaveersomani]

ha ha ha........... @xor lx

the same thing i was thinking there fore i asked for 2 more example..........


Regards
CA Mahaveer Somani

[fillefrans]

@ramananhrm

No, I dont always want to split after the second word…


@mahaveersomani

original A1: ELATION Volt Q3 spot
split to:
A1: ELATION Volt Q3
B1: spot

original A1: GLP impression 90 RGB 6er Tour Pack black
split to:
A1: GLP impression 90 RGB 6er Tour Pack
B1: black

original A1: PROLIGHTS 1° FLOOD filter for Cromowash3
split to:
A1: PROLIGHTS 1° FLOOD filter
B1: for Cromowash3

As you can see, the split point is not always the same. I know I have to manually specify the split point for each row, I'm just looking for an easier way than having to activate cell, select text, cut & paste to B1…

[Debraj Roy]

Hi fillefrans!

Welcome to the forum..

I think, it need to move to the VBA/MAcro section..

Try the attached..

Double click the cell, to activate EDit Mode ..
In the area where you need to split.. Insert a symbol ^, if you need to split more that one area.. you can add two or more ^ carret sign..

PROLIGHTS 1° FLOOD ^filter for ^Cromowash3
and press Enter..

it will overlap the already exist data in right columns..

Not a valid method.. but your requirement is also not a valid requirement..

Split Data where you need.xlsm


PS:

Please Login or Register  to view this content.

[fillefrans]

Try the attached..

Please Login or Register  to view this content.

Great, thanks! I guess that's as close as I'm gonna get, definitely helps a lot considering the spreadsheet is 1500 rows... :-D Thanks again!