Forum Discussion
Extracting the Words from the Cell
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 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 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?
- 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") 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.
