Splitting single cell data with no pattern

[tenbusi]

Hi all,

I have a sheet where i need to split messy data in a single cell ending up with two clean cells.

Here's an example:

[TABLE="width: 299"]
<tbody>[TR]
[TD="width: 299"]A V AUTO SALES is in cell A2The end goal is:
AV Auto in cell B2
Sales in B3

Here's another:
[TABLE="width: 299"]
<tbody>[TR]
[TD="width: 299"]R & D MOTORS
goal is:
R&D
Motors[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


There may be variations of how that data is spaced, how many spaces there will be, etc. Once all the data is split cleanly, i'll need to make sure the combined characters of both cells is 19 or less. I believe that can be done after the fact, but wanted to include it in case it can be performed in the same operation.

THANKS FOR THE HELP!

 

[Roderick_E]

without a pattern, it is impossible

 

[tenbusi]

i was hoping that wouldn't be the case!

What about if i remove all spaces, trim to 19 characters, then split the cells evenly. That may be ok for my purposes. what is the best formula(s) to achieve that goal?

 

[Jonmo1]

With just 2 examples to look at, the only pattern I can see is the split is before the last word in the cell (before Sales, and before Motors)
Is that consistent?

 

[tenbusi]

Unfortunately no. There are others that have trailing data/last words may or may not be significant. I think i may be able to combine all the data, trim to 19 characters, then split in half. it will look messy, but may be the best non-manual solution.

 

[Jonmo1]

You can do that like this
Original string in A2
B2: =LEFT(SUBSTITUTE(A2," ",""),10)
C2: =MID(SUBSTITUTE(A2," ",""),11,9)

 

[tenbusi]

Thanks! will give it a try!