SOLVED

Extracting the Words from the Cell

Copper Contributor

Hi all,

 

Here's my question:

There are some contents in cell B4 to B6. I would like to know is there any formula to extract the words and put them into cell C4 to C6? 

 

renjie0315_1-1675680950537.png

 

 

7 Replies

@renjie0315 If the strings are as consistent as the screenshot suggests you can use

=RIGHT(B4,LEN(B4)-9)

 

That strips the first 9 characters leaving whatever come after.

@Riny_van_Eekelen Unfortunately it's not consistent, some of the strings have -R1 for revision control. May I know do you have any method to counter this problem? Can I use IF statement to achieve it? 

 

renjie0315_0-1675682339247.png

 

best response confirmed by renjie0315 (Copper Contributor)
Solution

@renjie0315 Then perhaps this:

=RIGHT(B4,LEN(B4)-FIND(" ",B4)-1)

 

In stead of stripping off the first nine characters it finds the space between the code and the text.

 

If this is still not giving consistent results, please provide an example that includes all possible inconsistencies. There are other, more stable ways to do this kind of job.

@Riny_van_Eekelen Thank you for your prompt reply. Here are the conditions that I have summarized based on the data that I have. There are 8 conditions in total. Some of the contents have "-" after the quotation number but some of them don't have. However, it can be treated as 1 string. 

 

May I know do you have any method to extract the words only from the cells? 

 

renjie0315_1-1675691218555.png

 

 

@renjie0315 So the split needs to be made on the first space, sometimes on the second hyphen or sometimes on the third hyphen? You are not making it easy for yourself. Is there no way you can influence the quality of the Quotation No.?

 

Perhaps a very creative person here can think of a smart way to do what you need. I'm heading out soon and can't look at it now. Sorry.

try regular expressions
[-\s]([a-z\sA-Z]+)$
test
=webservice("http://e.anyoupin.cn/eh3/?preg_match~[-\s]([a-z\sA-Z]+)$~915-2022 Apple is~1")
=webservice("http://e.anyoupin.cn/eh3/?preg_match~[-\s]([a-z\sA-Z]+)$~" & b3& "~1")
1 best response

Accepted Solutions
best response confirmed by renjie0315 (Copper Contributor)
Solution

@renjie0315 Then perhaps this:

=RIGHT(B4,LEN(B4)-FIND(" ",B4)-1)

 

In stead of stripping off the first nine characters it finds the space between the code and the text.

 

If this is still not giving consistent results, please provide an example that includes all possible inconsistencies. There are other, more stable ways to do this kind of job.

View solution in original post