asked on

macro to split words to 5 columns

I have a column Title as shown in the attached excel OriginalColumn.
I need to split this column into 5 different columns with max of 35 characters each.
The rules are:
1)Maximum characters allowed in each of these split columns shouldn't exceed 35 characters.
2)The split columns as shown in sample data requiredsplitcolumn shouldn't be cut in middle of a word.

So, basically I need to split the words without cutting them in the middle ...but then for some bigger Titles if split columns exceeds more than 35 characters length it should be cut at a different point (e.g character 32) to reflect a complete word.
This applies to each of the five split columns.

Any ideas how to get my required Final resultset???
OriginalColumn.xlsx
RequiredSplitColumn.xlsx

David Johnson, CD

I'd split on the comma rather than the length (35)
The problem being is that your data is unstructured

nutsch

How about this?

B3:
=LEFT($A3,FIND("|",SUBSTITUTE(LEFT($A3,35)," ","|",LEN(LEFT($A3,35))-LEN(SUBSTITUTE(LEFT($A3,35)," ",""))))-1)

C3
=TRIM(IF(LEN($A3)-LEN($B3)-1<35,MID($A3,LEN($B3)+1,36),LEFT(MID($A3,LEN($B3)+1,LEN($A3)),FIND("|",SUBSTITUTE(LEFT(MID($A3,LEN($B3)+1,LEN($A3)),35)," ","|",LEN(LEFT(MID($A3,LEN($B3)+1,LEN($A3)),35))-LEN(SUBSTITUTE(LEFT(MID($A3,LEN($B3)+1,LEN($A3)),35)," ",""))))-1)))

D3
=TRIM(IF(LEN($A3)-(LEN($B3)+LEN($C3)+1)-1<35,MID($A3,(LEN($B3)+LEN($C3)+1)+1,36),LEFT(MID($A3,(LEN($B3)+LEN($C3)+1)+1,LEN($A3)),FIND("|",SUBSTITUTE(LEFT(MID($A3,(LEN($B3)+LEN($C3)+1)+1,LEN($A3)),35)," ","|",LEN(LEFT(MID($A3,(LEN($B3)+LEN($C3)+1)+1,LEN($A3)),35))-LEN(SUBSTITUTE(LEFT(MID($A3,(LEN($B3)+LEN($C3)+1)+1,LEN($A3)),35)," ",""))))-1)))

E3
=TRIM(IF(LEN($A3)-(LEN($B3)+LEN($C3)+LEN($D3)+2)-1<35,MID($A3,(LEN($B3)+LEN($C3)+LEN($D3)+2)+1,36),LEFT(MID($A3,(LEN($B3)+LEN($C3)+LEN($D3)+2)+1,LEN($A3)),FIND("|",SUBSTITUTE(LEFT(MID($A3,(LEN($B3)+LEN($C3)+LEN($D3)+2)+1,LEN($A3)),35)," ","|",LEN(LEFT(MID($A3,(LEN($B3)+LEN($C3)+LEN($D3)+2)+1,LEN($A3)),35))-LEN(SUBSTITUTE(LEFT(MID($A3,(LEN($B3)+LEN($C3)+LEN($D3)+2)+1,LEN($A3)),35)," ",""))))-1)))

jen Rad

ASKER

This is Perfect...Thanks nutsch!

Will test it with my data & get back to you shortly!

Thanks very Much!

jen Rad

ASKER

Hi nutsch... I have a quick question. I see that the above formula splits the Title to another column even if the length of the word is less than the 35 characters.

for Example...I have this sample Titles like shown below which are less than 35 characters...it is being split to different column? Can we just leave them in their cell if they the title length <35 in the same cell???

Sample titles that has length <35 but gets split to another column:
_______________________________________________________________________
David Cohn
Jon Milek
Mark Geth
Rick Hills
Rob Bale

Thanks very much!

nutsch

edit B3 formula to:

=IF(LEN($A3)<36,$A3,LEFT($A3,FIND("|",SUBSTITUTE(LEFT($A3,35)," ","|",LEN(LEFT($A3,35))-LEN(SUBSTITUTE(LEFT($A3,35)," ",""))))-1))

jen Rad

ASKER

Thank you so very Much!

It works amazingly well & exactly what I wanted:)...Just a few things I happened to notice...
I have attached the sample data excel for your reference.

For this sample data shown I am getting a little glitch in the splitcolumn Title_3. Any ideas???
If I could get a fix for this , it would really help:)
Also, I will need a formula for splitcolumn Title_5 as these Titles are sometimes are really big and might extend until Title_5.

I know the data I am working with is totally unstructured data:(.But I cannot alter anything on this data as it has some important details. So had to deal with this data as is as I have no other choice:(...my bad!

Thanks very much for helping!!!
SplitColumn-test2.xlsx

nutsch

in D2

=TRIM(IF(LEN($A2)-(LEN($B2)+LEN($C2)+1)-1<35,IF(LEN(B2)+LEN(C2)+1=LEN(TRIM($A2)),"",MID($A2,(LEN($B2)+LEN($C2)+1)+1,36)),LEFT(MID($A2,(LEN($B2)+LEN($C2)+1)+1,LEN($A2)),FIND("|",SUBSTITUTE(LEFT(MID($A2,(LEN($B2)+LEN($C2)+1)+1,LEN($A2)),35)," ","|",LEN(LEFT(MID($A2,(LEN($B2)+LEN($C2)+1)+1,LEN($A2)),35))-LEN(SUBSTITUTE(LEFT(MID($A2,(LEN($B2)+LEN($C2)+1)+1,LEN($A2)),35)," ",""))))-1)))

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jen Rad

ASKER

Nutsch...Thank you very much. Sure, I Will also try your above solution & let you know.

In the meantime, I was just testing my crappy data...and I came across few things. Maybe if you could review it
Attached is the file for your reference.

Thank you soo very much!!!...you are really great:)
SplitColumn-test3.xlsx
FewFixes.jpg

jen Rad

ASKER

Hi Nutsch...a quick question. Am sorry am not very familiar with the excel and relatively new to this.
After I added the above code to the new module & I trying to get this to run using Ctrl+F8. what am I missing? It doesn't show up with the name of the macro for me to choose to run. Any ideas??
I have attached the files and the screenshot ...for your reference.
Can you please provide me the step to step instructions to get this work on the excel?

Thank you very much...Nutsch!
RunMacroScreen1.jpg
AddedFormulaToB2Cell.jpg
SplitColumn-macrotest1.xlsm

nutsch

It's not a macro, it's a user defined function (udf). You copy it in a module like a macro, but you call it in a cell like other functions. In this case, write in cell b2

=get35chars($a2:a2)

jen Rad

ASKER

so..i copy it in cell b2 and dragged it down...and then what do I do for the other columns??Please advice!