Excel Character Extraction Techniques

Hey everyone! I'm Weixing Sauce who loves to use Excel to solve work problems!

I've shared a lot about data analysis before, and the skills of extracting data have been mentioned twice!

There's no way around it, as it's often needed!

Given that the previous two articles only mentioned it and didn't explain in detail the practical application of data extraction in work;

Today I'm going to do a special Q&A to introduce Excel in action - extracting specified characters!

After reading this article, I believe that next time you encounter such problems, you can all complete them more efficiently and quickly!

I have a friend, Little B, who runs an online store and usually uses Excel to count the sales situation of goods.

This is part of the data exported from the web page:

In order to facilitate the analysis of which style is the most popular, it is necessary to clean the above data, that is, to extract the effective content of the part of 「Three layers 70」.

▋TEXTBEFORE and TEXTAFTER functions

Using the formula to locate the position after the word 「lacquer」 to before the 「c」:

=TEXTBEFORE(TEXTAFTER(B2,lacquer),c)

The formula is simple and easy to understand.

But only Office365 and the new version of WPS can use it...

▋MID function

=MID(B2,FIND(No lacquer,B2)+2,SEARCH(cm,B2)-FIND(No lacquer,B2)-2)

Formula analysis:

=MID(B2,❶,❷)

❶ FIND(No lacquer,B2)+2

First use the FIND function to find the position of 「No lacquer」 and offset to the right by 2 characters, that is, skipping the word 「No lacquer」 itself, so the MID function starts to extract from the position of ❶;

❷ SEARCH(cm,B2)-FIND(No lacquer,B2)-2

Here we sort it out, that is, SEARCH(cm,B2)-❶, that is, use the SEARCH function to find the position of 「cm」 and calculate the number of characters after 「No lacquer」 to before 「cm」.

This formula is relatively complex, but in essence it is still the idea of 「extracting after X and before Y」.

▋VBA custom function

If you can't remember the formula of the old function, there is another way here.

Press Alt+F11 to open the VBA editor.

In the VBA editor, right-click on VBAProject and select 【Insert】-【Module】.

In the new module window, copy and paste the following code:

Function TB(text As String, search As String) As String    ' Find the position of the search string in the text    Dim pos As Integer    pos = Instr(1, text, search, vbTextCompare)    ' If found, return all text before the search    If pos > 0 Then        TB = Left(text, pos - 1)    Else        ' If not found the search, return an empty string        TB =     End IfEnd FunctionFunction TA(text As String, search As String) As String    ' Find the position of the search string in the text    Dim pos As Integer    pos = Instr(1, text, search, vbTextCompare)    ' If found, return all text after the search    If pos > 0 Then        TA = Mid(text, pos + Len(search))    Else        ' If not found the search, return an empty string        TA =     End IfEnd Function

Close the VBA editor and return to the Excel interface.

Then we can use the formula in Method 1.

=TB(TA(B2,lacquer),c)

Okay, today I shared three practical applications of character extraction.

No matter how many skills you learn, it's only an effective skill if it can be put into use in work!

This article is from the WeChat public account: Qiuye Excel (ID: excel100), author: Weixing Sauce