Hi
I have a spreadsheet which is being used for a wordpress import. it needs to import a custom excerpt which is limited by word count. Hence, I need to do an =LEFT statement which returns a cell's contents by word count rather than character count. Is this possible?
Regards
Matt
You can count the spaces by comparing the length of the cell to the length of the cell without spaces. And you can use SUBSTITUTE to remove the spaces to facilitate the comparison.
Suggest you post a sample workbook.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I've attached an example
Any help would be greatly appreciated
Cheers
in D3 try below
=LEFT(A3,FIND("@",SUBSTITUTE(A3," ","@",C3))-1)
in above function substitute will give "@" at 25th space and then find will find the location of 25th space which is 306 in your attachment now -1 of it is 305 and then left will take words
Last edited by hemesh; 01-29-2016 at 05:44 AM. Reason: edited by mistake
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Many thanks hemesh that works for most of my data. The only exception being when my main posts are exactly 25 words long is returns '#VALUE!'
in that case use below formula I think this will suffice for all your need
=IF(C3>25,LEFT(A3,FIND("@",SUBSTITUTE(A3," ","@",C3))-1),LEFT(A3,FIND("@",SUBSTITUTE(A3&" "," ","@",C3))-1))
Last edited by hemesh; 01-29-2016 at 05:44 AM. Reason: updated formula
Thanks again, were getting there. Apologies I should have said any posts of 25 words or less...because with your new formula, this now just leaves posts that are under 25 words with an error.
check i have updated formula above
There still seems to be a problem with all under 25
I have attached updated spreadsheet with your new formula and 3 examples over 25, exactly 25 and 25
@ Matt in that case
=IF(B3>C3,LEFT(A3,FIND("@",SUBSTITUTE(A3," ","@",C3))-1),A3)
above formula will check the word count and word count limit if word count is greater than limit that time it will take the data from the left upto word limit if it is less than that or equals to word limit than data from the whole cell will be taken is it what you are looking for
You've cracked it
Many thanks for your help
@ matt you are welcome ! and thanks for the feedback.
I would appreciate if you click bottom left corner of the post of all who helps you at this forum, this add to the reputation of users that can be seen in the form of green bar at the bottom of their name.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks