Excel is a frequently used office tool in work, which is very helpful for work. It can help you sort out data quickly and more intuitively. Many people are asking how to extract text with unfixed position from excel? The extraction of text is often used in work. Now let's take a look at the related process introduction.
Method of extracting text with unfixed position from excel
1. Functional method
Functions used: left (extracted from the left) Right Mid
Use the mid function to extract the 7th to 14th bits from the ID card.
2. Rapid filling method
The quick filling function Ctrl+E in Lili Excel applies the rules of handwriting examples to the cells you see, and can perform one key extraction, one key merge, and one key change of data. In WPS, it is not called quick fill but intelligent fill, but it means the same thing. The shortcut keys are Ctrl+E.
3. Sorting method
Column splitting is also a common data extraction function. It can be performed according to the number of characters or separator symbols. Unwanted data can be skipped without extraction. As shown in the figure below, I only extracted the birth date, but not the other two columns.
4、 Word Lookup
Copy the data to Word, open Search, click Advanced Search, use wildcards, and use the regular expression rules to find the birth date. The search code is 19?? [0-1] [0-9] [0-6] [0-9], find the content and copy it to Excel.
How to extract text with unfixed position from excel
1. Set Auxiliary Column
Copy the column to be extracted to the auxiliary column.
2. Select extraction area
Select the extraction area in the auxiliary column, open Search in the toolbar, and click Replace.
3. Set replacement content
In the search content, enter "* extracted characteristic value". For example, in this case, the extracted characteristic value is "address:", and the replacement content is left blank. Click Replace All.
4. Extract content
After clicking Replace, it can be found that the extracted feature value and the content before the feature value all disappear, and only the content after the extracted feature value is retained, which realizes the extraction of text with unfixed position.
Three methods of excel address reference
The reference of cell address in EXCEL formula includes relative reference, absolute reference and mixed reference.
1. Relative reference
The relative cell reference in a formula (for example, A1) is based on the relative position of the cell containing the formula and the cell reference. If the location of the formula cell changes, the reference will also change. If the formula is copied in multiple rows or columns, the reference will be automatically adjusted. By default, new formulas use relative references. For example, if you copy the relative reference in cell B2 to cell B3, it will automatically be adjusted from=A1 to=A2.
2. Absolute reference
An absolute cell reference in a cell (for example, $F $6) always refers to cell F6 at the specified location. If the position of the cell where the formula is located changes, the cell that is absolutely referenced will always remain unchanged. If the formula is copied in multiple rows or columns, the absolute reference will not be adjusted. By default, new formulas use relative references and need to be converted to absolute references. For example, if you copy the absolute reference in cell B2 to cell B3, it is $F $6 in both cells.
3. Mixed References
Mixed references have absolute columns and relative rows, or absolute rows and relative columns. Absolute reference columns are in the form of $A1, $B1, etc. Absolute reference line adopts A $1 B $1 and other forms. If the position of the cell where the formula is located changes, the relative reference changes, but the absolute reference does not change. If the formula is copied in multiple rows or columns, the relative reference will be automatically adjusted, while the absolute reference will not be adjusted. For example, if you copy a mixed reference from A2 to B3, it will adjust from=A $1 to=B $1.
Excel formula reference is often used to help improve work efficiency. The above is about the method of extracting text in unfixed positions from Excel. If you don't know, you can use the above method.
