• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split the words in a cell into two but using whole words

S

seansr

Member
I have a colum that contains the Full Product Description (A)
I want the split the contents of A into B & C Take the first 12ish Charatcters and copy to B and the remainder into C
However must be in whole words
For example




Flaked Salmon & Lemon Pot Flaked Salmon & Lemon Pot
Wasabi Tofu & Edamame Pot Wasabi Tofu & Edam Pot
GM Seasonal Drink Offer 12oz GM Seasonal Drink Offer 12oz
 
The issue here is identifying where the first description finishes
Flaked Salmon & Lemon Pot is 25 characters
Wasabi Tofu & Edamame Pot is 25 characters
GM Seasonal Drink Offer 12oz is 28 characters
 
Is there any way to have a , or some other known character placed at the end of the first text by your system?
eg:
Flaked Salmon & Lemon Pot, Flaked Salmon & Lemon Pot
Wasabi Tofu & Edamame Pot, Wasabi Tofu & Edam Pot
GM Seasonal Drink Offer 12oz, GM Seasonal Drink Offer 12oz
 
seansr said:
Flaked Salmon & Lemon Pot Flaked Salmon & Lemon Pot
Wasabi Tofu & Edamame Pot Wasabi Tofu & Edam Pot
GM Seasonal Drink Offer 12oz GM Seasonal Drink Offer 12oz
Click to expand...
As it's only a duplicate string, B & C are equals !
So the purpose is to cut string just before fisrt word is duplicated :

=LEFT(A1,FIND(LEFT(A1,FIND(" ",A1)),A1,FIND(" ",A1))-1)


If you really want only « first 12ish characters in whole words » :

formula in B1 : =LEFT(A1,FIND(" ",A1,12))

formula in C1 : =RIGHT(A1,LEN(A1)-LEN(B1)) …
​
 
I copied the above formula the first one works a treat the second one does nothing
I have taken a snap shot of some of the data
 

Attachments

  • text example.xlsx
    11.6 KB · Views: 8
Sorry Stupid Mistake,
Any Idea why I am getting the #VALUE! when the same formula is copied down all 2500 rows
 
#VALUE comes from formula searching space separated words,
so if you apply it when there is only one word without any space …

The reason why it's better to join within initial post a sample workbook
reflecting true data as well with a crystal clear and complete explanation !

At beginner level, you can mod yourself formula using IF, IFERROR, …
​
 
Other than IF, IFERROR, … to remove #VALUE

You also can use...

In Text 1 H2, formula copy down :

=LEFT(B2,FIND(" ",B2&" ",MIN(LEN(B2),12)))

and ,

in Text 2 I2, formula copy down :

=SUBSTITUTE(B2,H2,"")

Regards
Bosco
 
You must log in or register to reply here.
Back
Top