How to Convert Text in Excel

If you need to convert text in Excel, Text Toolkit is exactly what you need. Using the Convert Text utility, you can convert numbers stored as text to number format, replace irrelevant characters and line breaks with the chars you need, and even change the accented characters to their non-accented equivalents.

Before you start

We care about your data. The add-in will back up your file if you select the corresponding option.

How to convert text

Click the Convert icon on the Ablebits Data tab in the Text group:
Convert text in Excel.
You can see the Convert Text pane:
How to convert in Excel.

  1. Select the cells where you need to convert values.
  2. Click the Expand selection icon to highlight the whole table.
  3. Sometimes numerical data in worksheets is formatted as text - it often happens when digits are imported from a database. Choose the option Convert numbers stored as text to numbers to change text to number format in any range of your choice.
  4. Your spreadsheet may contain irrelevant characters that you need to replace with new ones. Use Convert characters [...] to [...] to avoid repeating the same steps. Enter all characters you want to change individually and type the character that will replace of the unwanted ones.
  5. Pick this option to convert line breaks to the characters you need. You can choose between standard replacements for the carriage returns in your Excel cells in the drop-down list: a semicolon, a comma, a dot/full stop, or a space. Or, enter your own character(s) in the field.
  6. This option will come in handy if you need to replace the accented chars with regular ones.
  7. We recommend always ticking off the Backup the worksheet checkbox so you never lose your data.

Click the Convert button to get your result.

Responses

Convert Numbers Stored as Text to Numbers. This function does not work. I am able to convert numbers stored as text to numbers using the Excel "pop-up box" (triangle with exclamation point then click on convert to numbers) but not able to convert with Ablebits function. After attempting to convert, there is a box with "no cells have been processed".

Reply

Hi, James,
Thank you for contacting us. I am sorry you are having difficulty with the Convert Text utility. And thank you for reporting the issue, of course, this is not the way Convert Text should behave!

For us to be able to investigate the problem, please send us your Excel book that contains the worksheet with your data that you tried to process with Convert Text. Please shorten your table to 10-20 rows. Also, please let us know the version of Excel you use and the Ultimate Suite (or Text Toolkit) version.

I have sent you an e-mail, please reply it with the above-mentioned information and we will do our best to fix the issue.

Thank you.

Reply

I copied and pasted large amount of data into spread sheet...

Everything came over as text... I've tried everything to convert to numbers....

Any suggestions?

Reply

Hi, Mike,
Thank you for your comment and for contacting us at support@ablebits.com. We have received your file and will contact you by email shortly.

Reply
GENE FERRIS says:
April 9, 2019 at 9:43 pm

I am working with Wild Apricot generated website with membership db. My issue is that the db fields are generated by individuals in many formats, Telephone numbers are entered as: xxx.xxx.xxxx or xxx-xxx-xxxx, or (xxx) xxx-xxxx etc.

I would like to export the numbers as a .csv file into Excel 19 , Reformat the list to the first example, and import the updates into WA db. (Without retyping hundreds of items)

Do your addins do functions such as that??

Thanks
Gene

Reply

Hello Gene,
Thank you for contacting us.

For us to be able to help you better, please send us a small sample of your .csv file with your source data and the result you expect to get to support@ablebits.com. I kindly ask you to shorten each table to 10-20 rows.

We'll look into your task and see if our software can help.

Reply

Hello. I love your tools. I use them regularly in my daily work routine in Excel. I'm currently facing a problem here trying to use the Convert Text tool. I have an excel file (exported from a database) that has a column containing numeric data. Excel shows them as text (and allows each cell to be individually converted to number using its small popup menu). Im trying to use your option to convert number stored as text into a proper number format, but it doesn't work. The tools scans my entire column and then says that no cells have been process. Could you please assist. Thanks.

Reply

Hello Faraz,
Thank you for contacting us.

Please try to install the latest version of Ultimate Suite and check if the problem persists there: https://www.ablebits.com/files/get.php?addin=xl-suite

If it doesn't help, please send us a sample workbook with your data for further investigation to support@ablebits.com. We'll do our best to help you.

Reply

Hello! Tell me the program only in English? Not in Russian?

Reply

Thank you for your question, Ryslan.

Our software is available in English only at the moment.

Reply
Sherrie Nelson says:
October 16, 2019 at 9:19 pm

In Convert, can Ablebits convert CR or LF or CRLF or all three? Everything I've read seems to imply it's just CR. I need it to do all!

Sorry if this has been asked a million times, but I don't have time to research. Thanks!

Reply

Hello Sherrie,

I'm sorry, I'm afraid we don't have any tool that could help you with the task.
But you can send us a sample of your source data and the result you'd like to get to support@ablebits.com. Please shorten your tables to 10-20 rows. We'll forward it to our developers to consider as a new feature.

Reply

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard