How to extract text and numbers in Excel
Ultimate Suite for Excel contains a tool that allows extracting text, substrings, and numbers from one cell to another 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 worksheet if you select the corresponding option.
How to extract text fragments 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 text fragments 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 select the whole column.
- Opt for 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 all digits to pull all numbers from the selected cells.
- Select the Insert as formula checkbox to get updated results if the source data changes.
- To make sure your data is safe, select Back up this worksheet.
Click Insert Results to see the extracted records in a new column inserted to the right of your source column.
Responses
mohsen says:
July 23, 2022 at 5:21 am
i want to extract text before first number
cold water 12 coffee
=>
cold water
Hello Mohsen,
Thank you for your comment. If the number is the same in the whole column, you can use Extract all before text option. In case the numbers may vary, you'll need to use our Regex Tools -> Extract add-in instead. Please try to use the following expression for your task:
\D+
Please also visit our help page on the tool to be able to correct the expression for your needs:
https://www.ablebits.com/docs/excel-regex-tools/
If you have any other questions or need further assistance, please feel free to email us at support@ablebits.com
Rekha says:
January 26, 2021 at 2:03 pm
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.
Reinhard Traunmüller says:
February 15, 2020 at 8:01 am
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 ;)
Ekaterina Pechyonkina (Ablebits Team) says:
February 15, 2020 at 7:00 pm
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.
Reinhard Traunmüller says:
February 11, 2020 at 10:14 am
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/
Reinhard Traunmüller says:
February 14, 2020 at 8:31 am
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!
Rob says:
January 10, 2020 at 12:55 pm
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.
Steve says:
October 25, 2019 at 9:35 am
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.
Katerina Bespalaya (Ablebits Team) says:
October 25, 2019 at 10:20 am
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.
Samuel Turner says:
June 24, 2019 at 2:13 pm
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?
Katerina Bespalaya (Ablebits Team) says:
June 25, 2019 at 7:16 pm
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.
Robert Sheard says:
February 18, 2019 at 9:26 am
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.
Robert Sheard says:
February 20, 2019 at 7:46 am
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 :)
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
