Hi,
I extract first 7 words from every cell using following formula:
It works but when I have cells with words less than 7 I got #VALUE! error.
=LEFT(A1,FIND("@@",SUBSTITUTE(A1," ","@@",7),1)-1)
How can I write IF statement that if cells has less than 7 words, cell will be just copied to new cell.
columna A Column B dad sddas sadsa dasdas ddadd sd 7 sddasd dad sddas sadsa dasdas ddadd sd 7 dasdas dsda sddas dasdas dsda sddas
Try to add more spaces into text cell (i.e : 7) to get unique text with at least 7 spaces:
=LEFT(A1,FIND("@@",SUBSTITUTE(A1&REPT(" ",7)," ","@@",7),1)-1)
Quang PT
Hi,
Just wrap your formula in an IFERROR() function. i.e
=IFERROR(LEFT(A1,FIND("@@",SUBSTITUTE(A1," ","@@",7),1)-1),A1)
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Try this
=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",125)),125*7))
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hi pomper,
Try this formula instead in B2 when your long strings are in A2.
=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),7*LEN(A2)))
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
pomper 
Register To Reply
Bookmarks