![\"\"](\"http:\/\/blog.uvm.edu\/tbplante\/files\/2019\/08\/image.png\")
<\/figure><\/p>\n<\/div><\/div>\n\n\n\n
…you’ll get <\/p>\n\n\n\n
![\"\"](\"http:\/\/blog.uvm.edu\/tbplante\/files\/2019\/08\/hr-as-string.png\")
<\/figure><\/p>\n<\/div><\/div>\n\n\n\n
My previous strategy has been to manually extract these numbers into rows and columns. I just came across these two pages of Excel strategies that will pluck the numbers in the second picture and produce the first one!<\/p>\n\n\n\n
Step 1: Split strings into separate columns. <\/h4>\n\n\n\n
Reference: https:\/\/support.office.com\/en-us\/article\/split-text-into-different-columns-with-functions-49ec57f9-3d5a-44b2-82da-50dded6e4a68<\/a> <\/p>\n\n\n\n Here, we’ll split the string at the spaces. There are two spaces, so you’ll end up with three new variables. First, make three new ‘temp’ columns to the right. You’ll need a different code for each section’s destination cell.<\/p>\n\n\n\n Left bit, or “0.80”:<\/p>\n\n\n\n Middle bit, or “(0.70,”:<\/p>\n\n\n\n Right bit, or “0.90)”:<\/p>\n\n\n\n This assumes that your string of interest is sitting cell A2. Change this cell reference as needed. If all goes well, you should have new cells that look like this:<\/p>\n\n\n\n Reference: https:\/\/www.ablebits.com\/office-addins-blog\/2017\/11\/22\/excel-extract-number-from-string\/<\/a> <\/p>\n\n\n\n Welp, there’s still some non-numeric text here. Time to pluck out the raw numbers! We’ll pretend that the ‘hrtemp’ cell also has a non-numerical character in it (e.g., a percent sign) for completeness’ sake. (Excel actually considers it a string still, which is why it’s not showing as “0.8”). Make 3 new rows to the right and use this code to extract the raw numbers contained in the string:<\/p>\n\n\n\n …obviously, you’ll need to change the B2 cell to C2 and D2 as needed. You should get: <\/p>\n\n\n\n <\/p>\n<\/div><\/div>\n\n\n\n Boom! You should be able to copy to strings in subsequent rows by hovering over the bottom right of each cell and dragging down. Now you can “import excel” in stata and use your hr, low95, and high95 variables!<\/p>\n","protected":false},"excerpt":{"rendered":" Stata’s great at taking raw numbers and chugging out graphs with minimal edits. Often times you’ll get results that aren’t raw numbers, but instead will exist as a string. Instead, of getting: …you’ll get My previous strategy has been to manually extract these numbers into rows and columns. I just came across these two pages … Continue reading Extracting numbers from strings in Excel<\/span><\/a><\/p>\n","protected":false},"author":4473,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-301","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/blog.uvm.edu\/tbplante\/wp-json\/wp\/v2\/posts\/301","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.uvm.edu\/tbplante\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.uvm.edu\/tbplante\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.uvm.edu\/tbplante\/wp-json\/wp\/v2\/users\/4473"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.uvm.edu\/tbplante\/wp-json\/wp\/v2\/comments?post=301"}],"version-history":[{"count":5,"href":"https:\/\/blog.uvm.edu\/tbplante\/wp-json\/wp\/v2\/posts\/301\/revisions"}],"predecessor-version":[{"id":570,"href":"https:\/\/blog.uvm.edu\/tbplante\/wp-json\/wp\/v2\/posts\/301\/revisions\/570"}],"wp:attachment":[{"href":"https:\/\/blog.uvm.edu\/tbplante\/wp-json\/wp\/v2\/media?parent=301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.uvm.edu\/tbplante\/wp-json\/wp\/v2\/categories?post=301"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.uvm.edu\/tbplante\/wp-json\/wp\/v2\/tags?post=301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}=LEFT(A2, SEARCH(\" \",A2,1))<\/code><\/pre>\n\n\n\n=MID(A2,SEARCH(\" \",A2,1)+1,SEARCH(\" \",A2,SEARCH(\" \",A2,1)+1)-SEARCH(\" \",A2,1))<\/code><\/pre>\n\n\n\n=RIGHT(A2,LEN(A2)-SEARCH(\" \",A2,SEARCH(\" \",A2,1)+1))<\/code><\/pre>\n\n\n\n![\"\"](\"http:\/\/blog.uvm.edu\/tbplante\/files\/2019\/08\/image-1.png\")
<\/figure>\n\n\n\nStep 2: Pluck numbers from strings. <\/h4>\n\n\n\n
=(SUMPRODUCT(MID(0&B2, LARGE(INDEX(ISNUMBER(--MID(B2, ROW(INDIRECT(\"1:\"&LEN(B2))), 1)) * ROW(INDIRECT(\"1:\"&LEN(B2))), 0), ROW(INDIRECT(\"1:\"&LEN(B2))))+1, 1) * 10^ROW(INDIRECT(\"1:\"&LEN(B2)))\/10))\/100<\/code><\/pre>\n\n\n\n![\"\"](\"http:\/\/blog.uvm.edu\/tbplante\/files\/2019\/08\/finished-hr-extraction.png\")
<\/figure>