A fun challenge.

Miscellaneous Forums/General Discussion/A fun challenge.

On another forum someone had the following problem in excel and was trying to devise a solution.

Problem:
Column A is a field of text of max 30 characters. He wanted to split column A into columns B & C as close to the middle as possible, around a space. (i.e. "This is a test!", would be split into "This is " and "a test!", 8 characters and 7 characters long, respectively.)

Also, no 'intermediate cells' can be used in the calculation. Only one cell, one formula.

Given that you can't create variables in excel, and you can't use loops, it becomes an interesting problem to try and condense.

Challenge: Derive a solution that works flawlessly. In fact, I'll make this a bit more difficult - derive a solution that works flawlessly on text of any given length with any number of spaces. You can assume that one space will be present in the text. The code should never return an error to the cell (although inner code can return an error if it's handled.)

I've got a solution that I think is flawless - I've got to test it a bit more. I tell you, it is one UNWIELDY chunk of code! (ack, a test just revealed my code is not flawless. It split a 78 character string into a 43,35 combo, while it could have been split 37, 41. )

Oh yeah, try to keep your code as short as possible too. I don't think any solution will be "short", but try to come up with the shortest method possible.

(obviously no using the Call() function to send the text to a DLL or something ;)

I'll post my code later, either in an upgraded form If I can or in it's current "close but no cigar" form.

Luke.H

I’m not sure I get it, why cannot you make a custom function or macro in vba, is it just for the challenge?

Aymes

So, just to clarify here,

This is a challange using purely worksheet functions and no VBA at all?

SculptureOfSoul

Correct Aymes and Luke. Just for a challenge.

SculptureOfSoul

And to be honest I don't know jack about excel. I've got to look up making macros (didn't know that was possible, hehe.)

ashmantle

nerd alarm! nerd ala.... oh, wrong forum ;p

ImaginaryHuman

This is easy.

1) Open the Excel file with the column of data
2) Create one or more empty columns to the right of the data depending on how many split-off segments you want (one split means one extra column)
2) Select the entire data column by clicking on the column label
3) Go to the `Data` menu and choose "Text to columns..."
4) Choose the "Fixed width" option then press `Next`
5) On the next screen you can click where you want the breaks to be - you can click at position 10 if you want to split it at the 10th character - then press `Next`
6) You have an option of choosing the output format, I usually use `General`
7) Press `Finish` and your data colum will be split at the positions you chose and the split data will be put into the adjacent columns. Just be careful you have enough empty columns to accept the data or you will overwrite other stuff
8) Alternatively you can split the data based on delimited data and can choose `space` as the delimiter, which will split off all separate words into their own columns

If you want to make a program of it just record it as a macro.

Is this what you wanted, or did you want it to detect the space and split the text at the closest space to the middle of the string?

SculptureOfSoul

Is this what you wanted, or did you want it to detect the space and split the text at the closest space to the middle of the string?

Yeah, that was the difficult part - split at the space closest to the middle.

Although, for his purposes your solution may very well work. I'll mention it to him

The challenge still stands!

mrtricks

Put your string in A1...

Put this in B1:

=LEFT(TRIM(A1),SEARCH(" ",TRIM(A1),LEN(TRIM(A1))-SEARCH(" ",TRIM(A1),(LEN(TRIM(A1))/2)+0.5))-1)

Put this in C1:

=RIGHT(TRIM(A1),LEN(TRIM(A1))-SEARCH(" ",TRIM(A1),LEN(TRIM(A1))-SEARCH(" ",TRIM(A1),(LEN(TRIM(A1))/2)+0.5)))

This includes the TRIM command which gets rid of all spaces apart from single spaces between words.

[edit] Just realised it fails if there are no spaces in the second half of the string. I'm working on a fix.[/edit]

ImaginaryHuman

#10

Who is going to want to do a visual basic challenge in a blitz forum? lol

He can probably get it to do what he wants using combination of delimited and fixed width text-to-columns.

mrtricks

#11

I can't figure it out. If you have at least one space in the second half of the string, my codes above work. If not, it fails and I can't work out a fix. Excel would need some commands which it doesn't have, i.e. searching through a string backwards, or reversing a string, or counting the number of searched strings within a string, and parsing through a certain number of search strings... without those I'm not sure what you ask can be done with functions alone. (Hang on, just seen that you have a working prototype - I'd be interested to see it)

SculptureOfSoul

#12

Pretty slick code MrTricks. Much more elegant than mine.

Unfortunately it fails if there isn't a space after the halfway point of the text. (i.e "Prod Description" fails).

SculptureOfSoul

#13

Well, I'll post my code and uh, if you're brave you can try and tweak it.

Some things I know my code is missing.
a call to Trim(). Didn't think to add it, prolly should.

Error handling when there are no spaces. My code will fail.

Gets close to center. As the string gets longer, the potential margin of error grows.

Anyhow, here goes.
A1 = the text to be parsed.
B1 = left half
C1 = right half

I'm going to copy and paste the post I made at the other forum.

I've got a single cell formula that will give perfect* results on strings of any length. It will also split them as perfectly* down the center as it can.

This was a fun problem. Programming is tough when you can't use true variables and loops . Anyhow, this is the fruits of 30 mins of work:

Assume the following:
A1 = product description
B1 = first half of description
C1 = Second Half of description

Now, the formulas (mentioning B1 last since it's easier to clarify C1 first)
A1: Your text
C1:
-------------------------------------------------------------------------------
=RIGHT( A1, LEN(A1)-LEN(B1))
-------------------------------------------------------------------------------
B1 is calculated and already contains the left half of the word. This gets the remaining right half.

B1:
----------------------------------------------------------------------------------
=IF( ISERROR(IF( (LEN(A1)/2) - FIND(" ", A1, LEN(A1)/3) > ( FIND(" ", A1, LEN(A1)/2) - ((LEN(A1)/2))),LEFT(A1, LEN(A1)/2 + FIND(" ", A1, LEN(A1)/2) - ((LEN(A1)/2))),LEFT( A1, FIND(" ", A1, LEN(A1)/3)))),LEFT( A1,FIND( " ",A1, 1)),IF( (LEN(A1)/2) - FIND(" ", A1, LEN(A1)/3) > ( FIND(" ", A1, LEN(A1)/2) - ((LEN(A1)/2))),LEFT(A1, LEN(A1)/2 + FIND(" ", A1, LEN(A1)/2) - ((LEN(A1)/2))),LEFT( A1, FIND(" ", A1, LEN(A1)/3))))
-----------------------------------------------------------------------------------
Uh, where to start. Basically this chunk of text is so long because it has to handle errors, and the if's involved required me to copy and paste a lot of text. Here's a pseudo-code breakdown

Start searching at 1/3 point of string. Find first space from this point on. Let's call this SPACE_1/3 for reference.

Start searching at halfway point of string. Find first space from this point on. Let's call this SPACE_1/2.

Calculate the length of half of the string. Let's call this HALFVALUE

Test the following
If( HALFVALUE - SPACE_1/3
> is greater than
SPACE_1/2 - HALFVALUE)

If True, return
Left half up to SPACE_1/2

if False return
Left half up to SPACE_1/3

So basically, it tests to see which of the two spaces is closer to the midpoint, and then returns the half that splits closer to the midpoint.

Then, C1, the second column, takes the other half (displayed in the formula for C1 above).

The rest is error handling (in case there aren't two spaces in the text, etc).

NOTE: This code will fail if there isn't a single space, and I didn't add error handling for that yet. Let me know if you need it.

---------------------The technicalities------------------------------------

*This code isn't perfect and doesn't always split in the exact middle. (If only there was a way to specify a left direction search from a given starting point - the code would have been trivial (and perfect) then. Bah!)

However, with a limitation of seven nested if's and trying to fit this all into a single cell, it does split it perfectly down the middle about 95% of the time , and will never ever split a word itself in half.

The exact middle point problem only shows up with long pieces of text with a lot of spaces or small words. One of my tests revealed this error (i'll paste it below to show that it's not very bad at all)

Starting string:
This is a test! This should work and split as close to the middle as possible.

Column 1
length: 43
"this is a test! This should work and split "

Column 2
length: 35
"as close to the middle as possible."

The ideal results would have been:

Column 1
length: 37
"this is a test! This should work and "

column 2
length: 41
"split as close to the middle as possible."

mrtricks

#14

You've put a bee in my bonnet about this now! I'll have another go when I have a moment at work...