Ultimate Suite for Excel contains a tool that allows extracting text, substrings, and numbers from one cell to another cell in your Excel worksheet. Forget about complex formulas with the FIND, MIN, LEFT, or RIGHT functions. Extract Text is a perfect alternative that offers you several radio buttons for when you want to extract text or numbers.
Before you start
We care about your data. The add-in will back up your file if you select the corresponding option.
How to extract substrings and numbers in Excel
Click the Extract icon that you'll find in the Text group on the Ablebits Data tab:
You'll see the Extract Text pane:
- Select the column with values where you want to extract text or numbers.
Note. The column with the extracted substrings will be inserted to the right of the initially selected column. For example, if you extract characters or substrings from column A, the result will be placed to a new column B.
- If needed, click the Expand selection icon to highlight the whole table.
- Tick The first N characters to enter the number of characters to extract from the beginning of the selected cells.
- Select The last N characters and specify the number of characters to extract at the end of your cell values.
- You can Extract text by position. Enter the necessary Position number of the first character and The number of characters to extract.
For example, you've got a 'ABC123DE' string and need to extract '123'. In this case, Position number of the first character will be '4' and The number of characters to extract will be '3'.
- All before text. Type the text that has the substring to extract in front of it.
- All after text. Specify the text that has the substring to extract behind.
-
You can Extract text between value 1 and value 2. For this, enter both values into the corresponding boxes. If you check off the Including delimiters option, the text will be extracted together with the values you entered. If you do not check it, the values will not be included. If the text case matters, make sure to check the Case-sensitive box.
- Choose Extract numbers to pull all numbers from the selected cells.
- Tick the Insert as formula checkbox to recalculate the resulting numbers if the source data changes.
- To make sure your data is safe, check the option to Back up the worksheet.
Click Insert results to see the extracted records in a new column inserted to the right of your source column.
Responses
Hi,
I have huge ticket data with the short description field and i want specific text should extract from the short description and copy that specific text in the last column
e.g."Ethernet1/34 S: Server Bandwidth" in this example i want all "ethernet" text copy from the description field and past "Interface Down Alert"to the last column
Similarly there are another categories which require to search from the description and paste in the last column with the sub categories
Hi Rekha,
Thank you for contacting us. If we got you right, the combination of several tools from Ultimate Suite may help you solve your task. However, for us to be able to recommend you particular steps to take, please send us a small sample workbook with 2 sheets: 1 - your source data and 2 - the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
Note! The result sheet is of great importance and often gives us a better understanding of your task rather than any text description. Please don't forget to include it.
We'll look into your data and try to find the solution for you.
For some reason, I can no longer change the characters or strings or sub-strings for all the extracts, splits, removes, split text or other similar items. What could be wrong?
I added them all these parameters before, but now they are fixed and cannot be changed or added to or removed.
Hello Courtney,
Thank you for contacting us. Sorry to hear that you've encountered this problem. The issue you're reporting was caused by one of the Windows updates. We have already fixed it, please uninstall the current version of Ultimate Suite via Control Panel -> Programs and Features, install the latest update and check if you have the same problem with the new version.
You can always download the latest version of Ultimate Suite from here: https://www.ablebits.com/downloads/index.php
If you're still having any difficulties, please email us to support@ablebits.com. Thank you.
Hi Mary,
I have (several) line breaks within a cell. For example, when I want to extract some characters from Line A and some characters from Line B, which are seperated by a line break, how can I simulate the line break (or a backspace)?
Because, when I copy the characters that need extracted from Line A and Line B from the cell and paste it in 'Extract Text', the line break is not copied over. I hope, I explained that clearly 😉
Hi Reinhard,
Thank you for contacting us again. I'm sorry but it is impossible to insert the line break/backspace into the text field of the add-in. Still, if you specify the word preceding the line break in your cell in the All after text field the tool will extract the substring behind along with this line break. I hope it will be of help to you.
Hi, is there a possibility that the extracted text/numbers will not be placed in a new column to the right, but to be APPENDED to the end of an already existing column to the right?
Hello Reinhard,
Thank you for contacting us. Sorry, but the add-in can't append the extracted text to the existing column. However, you can run the Merge Cells tool afterward and add the extracted text to the necessary column in a click. Here is the manual for Merge Cells for you just in case: https://www.ablebits.com/docs/excel-merge-cells/
Thanks, Mary. Did that already prior to your answer 😉
Another question: How can placeholders like the ? and * be used in for example 'All after text'?
Reinhard,
Glad to hear you found a solution before you've got my answer. 🙂
As for your question, characters like ? and * can be used as standard symbols (not as wildcards). If however, this is not exactly what you meant, please specify. Thank you!
In cell B13 I have =91.8+48+31.1
I want to extract the number 48 and insert it in another cell that has =B5+B8+B18+B19+B20+B22-204.39
in other words add 48 to this cell
tried extracting but obviously do not understand this tool....I must convert the extracted 48 from text to a number?
Hello Rob,
Thank you for contacting us. For us to be able to understand your task better, please send us a small sample workbook with 2 sheets - your source data and the result you expect to get - to support@ablebits.com. Just about 10-20 rows will be enough. We'll look into it and try to find a solution for you.
I have the same question. I need to extract the first name from an email address, and can not enter a . as the string before which to extract. Would be usefull if solution are posted here also.
Hello Steve,
Thank you for your question.
Please let us know what version of Ultimate Suite is currently installed in your Excel (Ablebits Data -> Help -> About). Also, please send us a small sample workbook with your source data and the expected result to support@ablebits.com. We'll help you further. Thank you.
I need to extract software version numbers from a large list. I can get the numbers, but not the "dot" between numbers like 2.1 extracts to 21 or 10.5.1 extracts to 1051.
Then the spacing between numbers also is lost like "MobileApp Connector for Configuration Manager 2007 1.0" extracts to 200710.
I think that the number extraction needs to be "dot" and "spacing" sensitive.
Thoughts?
Hello Samuel,
Our support service assistant has replied to you via email, please check your Inbox. If you have any questions, do not hesitate to address them to support@ablebits.com. Thank you.
I have just upgraded to the latest version. I when I extract numbers from within a string, it concatenates all numbers into one single number where these numbers are in separate strings within the target cell in the master data. This a blunt instrument.
e.g. AB-123-345 50 x 25mm becomes 1233455025 , and BR-012.123.01 BAK01 becomes 121230101
Q. Please advise me how I can extract numbers into individual strings or comma separated etc.
Many thanks.
Robert
Hi Robert,
Thank you for using our product and for your question. It is true, the add-in really extracts only numbers without any other characters, it was created for such use cases when characters are not needed. Concerning your question, if you have the same data format in your column, you can select to extract the first or the last number of characters. In this case, you will get extracted a string with all the symbols preserved.
Please contact us if you have any other questions or comments.
Thank you.
Thanks for your reply. Unfortunately, the data I work with can have numbers in different positions in each row. Most cells in the colum holding “Description”
have a mixture of numbers that represent a “manufacturer part number” and some represent size or other product attributes.
I had a play with using the extract Names and Address tool which does get a little closer by splitting different strings into columns (or rows) but not what I need to achieve.
My excel skills are intermediate plus, but I do not have the knowledge to solve this. Please can one of your experts have a look at this. If you need further clarity on my objectives, please contact me. Many thanks.
Hi Robert,
I think we've found a solution for you, please check your Inbox 🙂