{"id":1221,"date":"2011-10-07T11:02:00","date_gmt":"2011-10-07T15:02:00","guid":{"rendered":"http:\/\/exceltheatre.com\/blog\/archives\/2011\/10\/07\/remove-characters-before-numbers-excel-questions\/"},"modified":"2023-03-03T09:29:04","modified_gmt":"2023-03-03T14:29:04","slug":"remove-characters-before-numbers-excel-questions","status":"publish","type":"post","link":"https:\/\/exceltheatre.com\/blog\/archives\/2011\/10\/07\/remove-characters-before-numbers-excel-questions\/","title":{"rendered":"Remove Characters Before Numbers: Excel Questions"},"content":{"rendered":"<p>Questions are often asked in the comments for the Contextures Excel Blog, and sometimes those questions get overlooked, and lost in the shuffle.<\/p>\n<p>I&#8217;ll post some of the questions here, and please add your suggestions for solving the problems.<\/p>\n<h3>How Do I Remove Characters?<\/h3>\n<p>Here is one of those questions, from a commenter named Joanne:<\/p>\n<ul>\n<li>What formula do I use if I want to remove asterisks (*) in front of figures in a spreadsheet column, where the number of digits vary?<\/li>\n<li>For example, this is in cell A2:&nbsp; ***2.00 &#8211; ***20000.00<\/li>\n<\/ul>\n<h3>Solution 1 &#8211; Formula<\/h3>\n<p>To get rid of asterisks in those cells, I&#8217;d use the <strong><a href=\"https:\/\/www.contextures.com\/excelreplacesubstituteexamples.html\">SUBSTITUTE function<\/a><\/strong>.<\/p>\n<ul>\n<li>For example if the first number is in cell A2, put this formula in cell B2:<\/li>\n<\/ul>\n<p><code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <strong>=--SUBSTITUTE(A2,\"*\",\"\")<\/strong><\/code><\/p>\n<ul>\n<li>Then, copy that formula down to the remaining rows<\/li>\n<\/ul>\n<p>The SUBSTITUTE function replaces the asterisks with nothing, and the two minus signs after the equal sign change the number from text to a real number.<\/p>\n<h3>Solution 2 &#8211; Formula (Excel 365)<\/h3>\n<p>If you have Excel 365, with TEXTJOIN, and the new array functions, there is a formula on my Contextures site that will remove ALL non-numeric characters from a text string.<\/p>\n<p>Go to <a href=\"https:\/\/www.contextures.com\/xlcombine01.html\" target=\"_blank\" rel=\"noopener\">the Combine Text and Numbers page<\/a>, and in the TEXTJOIN section, you can see the formula and its explanation &#8211; Example 6 &#8211; Remove Non-Numeric Characters<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-4446\" src=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2011\/10\/textjoinnumbersonly01.png\" alt=\"TEXTJOIN function combines numbers only from cell \" width=\"291\" height=\"240\"><\/p>\n<h3>Suggested Solution &#8211; Find and Replace<\/h3>\n<p>Thanks to Jennifer Deacon, who suggested the following Find and Replace solution, if you don&#8217;t need to keep the original data, and want to remove the asterisks only:<\/p>\n<ul>\n<li>First, select all the cells where you want to remove asterisks<\/li>\n<li>Next, use the keyboard shortcut, Ctrl+H, to open the Find and Replace dialog box, with the Replace tab active<\/li>\n<li>Then, in the <em><strong>Find What<\/strong> <\/em>box, <strong>type this: ~*<\/strong>\n<ul>\n<li>The tilde (~) tells Excel to look for the asterisk (*)<\/li>\n<li>Otherwise, the * is used as a wildcard, representing &#8220;any characters&#8221;<\/li>\n<\/ul>\n<\/li>\n<li>Leave the <em><strong>Replace With<\/strong> <\/em>box empty\n<ul>\n<li>Excel will replace each asterisk with nothing<\/li>\n<\/ul>\n<\/li>\n<li>Finally, click the Replace All button, ro remove the asterisks from the selected cells<\/li>\n<\/ul>\n<h3>Your Solution<\/h3>\n<p>How would you solve the problem? If you have a different solution, please add a comment, to help Joanne, or anyone else with a similar problem.<\/p>\n<p>________<\/p>\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Questions are often asked in the comments for the Contextures Excel Blog, and sometimes those questions get overlooked, and lost in the shuffle. I&#8217;ll post some of the questions here, and please add your suggestions for solving the problems. How Do I Remove Characters? Here is one of those questions, from a commenter named Joanne: &hellip; <a href=\"https:\/\/exceltheatre.com\/blog\/archives\/2011\/10\/07\/remove-characters-before-numbers-excel-questions\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Remove Characters Before Numbers: Excel Questions&#8221;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_kadence_starter_templates_imported_post":false,"footnotes":""},"categories":[23],"tags":[],"class_list":["post-1221","post","type-post","status-publish","format-standard","hentry","category-excel-tips"],"_links":{"self":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/1221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/comments?post=1221"}],"version-history":[{"count":5,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/1221\/revisions"}],"predecessor-version":[{"id":4475,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/1221\/revisions\/4475"}],"wp:attachment":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/media?parent=1221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/categories?post=1221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/tags?post=1221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}