Microsoft Excel Tutorial: Why Are SEC Edgar Numbers Showing as Text in Excel?
In this episode of the Learn Excel podcast, MrExcel dives into a common issue when working with numbers copied from the SEC Edgar database. Sometimes, Excel displays these numbers as text, causing frustrating #VALUE! errors during calculations. We'll take a quick break from Copilot and explore why this happens and how to fix it!
The problem? Non-breaking spaces (character 160) sneak into your data when copying from sources like SEC Edgar. Even though they look like regular spaces, they can cause Excel to treat numeric data as text. And, unfortunately, Excel's CLEAN function won't remove these sneaky characters. But don’t worry! MrExcel walks through a simple workaround using Find and Replace with ALT + 0160 to remove the offending characters.
If you caught yesterday's mind-blowing episode (2652) on using Copilot for financial analysis, you'll know it's essential to verify the accuracy of AI-generated data. This issue with text numbers makes that even more important. Copilot may give answers that appear correct but are based on text rather than actual numbers, so always double-check!
In this episode, we’ll cover how to spot the problem, use Excel functions like LEN and CODE to investigate, and finally, how to get those numbers back in working order. With this trick in hand, you’ll be able to avoid errors and streamline your financial analysis process.
Subscribe for more Excel tips, and don’t forget to check out our new left-handed and right-handed coffee mugs so you can share the Excel love with your coworkers! Thanks for watching, and keep Excelling!
Spreadsheet Coffee Mugs and Shirts: MrExcel
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Why are SEC Edgar numbers coming in as text in Excel
(0:20) Apology - offering left-handed Excel mugs now
(0:36) Yesterday's amazing Financial Statements video
(0:52) Need to Verify anything coming out of an LLM
(1:20) Verifying yesterday's Copilot results
(1:50) Division in Excel causes #VALUE! error.
(2:00) ISNUMBER function in Excel
(2:10) Checking Status Bar in Excel to see if numeric
(2:33) Getting ASCII code for each character in the cell using MID, SEQUENCE, and CODE
(3:30) Checking ASCII code of a comma
(3:40) Non-Breaking Spaces Character 160 in Excel
(4:10) Why won't CLEAN function fix character 160?
(4:47) Typing Non-Breaking Space in Excel Find and Replace dialog
(5:00) Typing Alt 0160 and releasing Alt
(5:33) Copilot did Math on Text Numbers Will it still work with numbers?
(6:09) Testing same prompts in Copilot as yesterday
(6:20) Bug in Copilot showing formatting numbers
(6:39) SEC Edgar numbers being text likely have Non-Breaking Spaces
(7:03) Wrap-up
This video answers these search terms:
Excel SEC Edgar text to number fix
How to remove non-breaking spaces in Excel
#VALUE! error in Excel financial analysis
Fix text numbers in Excel SEC data
Excel find and replace non-breaking space
Convert text to numbers in Excel from SEC Edgar
Character 160 non-breaking space Excel fix
Excel financial analysis text number error
Troubleshoot SEC Edgar data in Excel
Excel #VALUE! error with text numbers
Excel LEN and CODE functions for data cleaning
Copilot financial analysis Excel text fix
In this episode of the Learn Excel podcast, MrExcel dives into a common issue when working with numbers copied from the SEC Edgar database. Sometimes, Excel displays these numbers as text, causing frustrating #VALUE! errors during calculations. We'll take a quick break from Copilot and explore why this happens and how to fix it!
The problem? Non-breaking spaces (character 160) sneak into your data when copying from sources like SEC Edgar. Even though they look like regular spaces, they can cause Excel to treat numeric data as text. And, unfortunately, Excel's CLEAN function won't remove these sneaky characters. But don’t worry! MrExcel walks through a simple workaround using Find and Replace with ALT + 0160 to remove the offending characters.
If you caught yesterday's mind-blowing episode (2652) on using Copilot for financial analysis, you'll know it's essential to verify the accuracy of AI-generated data. This issue with text numbers makes that even more important. Copilot may give answers that appear correct but are based on text rather than actual numbers, so always double-check!
In this episode, we’ll cover how to spot the problem, use Excel functions like LEN and CODE to investigate, and finally, how to get those numbers back in working order. With this trick in hand, you’ll be able to avoid errors and streamline your financial analysis process.
Subscribe for more Excel tips, and don’t forget to check out our new left-handed and right-handed coffee mugs so you can share the Excel love with your coworkers! Thanks for watching, and keep Excelling!
Spreadsheet Coffee Mugs and Shirts: MrExcel
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Why are SEC Edgar numbers coming in as text in Excel
(0:20) Apology - offering left-handed Excel mugs now
(0:36) Yesterday's amazing Financial Statements video
(0:52) Need to Verify anything coming out of an LLM
(1:20) Verifying yesterday's Copilot results
(1:50) Division in Excel causes #VALUE! error.
(2:00) ISNUMBER function in Excel
(2:10) Checking Status Bar in Excel to see if numeric
(2:33) Getting ASCII code for each character in the cell using MID, SEQUENCE, and CODE
(3:30) Checking ASCII code of a comma
(3:40) Non-Breaking Spaces Character 160 in Excel
(4:10) Why won't CLEAN function fix character 160?
(4:47) Typing Non-Breaking Space in Excel Find and Replace dialog
(5:00) Typing Alt 0160 and releasing Alt
(5:33) Copilot did Math on Text Numbers Will it still work with numbers?
(6:09) Testing same prompts in Copilot as yesterday
(6:20) Bug in Copilot showing formatting numbers
(6:39) SEC Edgar numbers being text likely have Non-Breaking Spaces
(7:03) Wrap-up
This video answers these search terms:
Excel SEC Edgar text to number fix
How to remove non-breaking spaces in Excel
#VALUE! error in Excel financial analysis
Fix text numbers in Excel SEC data
Excel find and replace non-breaking space
Convert text to numbers in Excel from SEC Edgar
Character 160 non-breaking space Excel fix
Excel financial analysis text number error
Troubleshoot SEC Edgar data in Excel
Excel #VALUE! error with text numbers
Excel LEN and CODE functions for data cleaning
Copilot financial analysis Excel text fix
Transcript of the video:
Just down below the video, if you click “LiKe”, that will make sure that YouTube shows this video to more people, Thanks!
Learn Excel from MrExcel podcast, episode 2654 – Why are these SEC Edgar numbers all text in Excel?
Hey, we're going to take a sort of break from Copilot today to talk about this problem.
But first my apologies to the folks out there who are left-handed and hold their coffee mugs in the other hand. And you want to have that message showing to your coworkers. We now have left-handed or right-handed coffee mugs so the message can be out. Okay.
Yesterday's video, 2652 – Financial analysis using Copilot in Excel was mind blowing.
It was absolutely amazing. Again, thanks to Dr. Nitin and his channel for that. But we're in this strange period in history where copilot and other LLMs can solve things really quickly.
They're growing by leaps and bounds. But if you're in a situation where the numbers actually matter. You need to go back and verify that Copilot is correct. Which really, if you think about it, causes you to go to Copilot, get the answers, and then go verify all the answers.
It takes longer than just performing the analysis yourself.
Now that will change. It will get to the point where we can trust this. But right now, we're not at the point where we can trust it. So yesterday here was the financial statement.
Here's the answers that I got from Copilot. I copied these from Copilot.
Paste them into a text box here. And I'm going to go through and verify all of these before I post the video to make sure that it's actually working.
So Current Ratio, we need Current Assets divided by Current Liabilities.
So simple enough. Point to current assets.
Point to current liabilities. And I should have already known I was in trouble because they are left justified. What the heck is up with that?
And sure enough, when I divide this by that, I get a #VALUE! error.
What the heck? So =ISNUMBER and click on that cell and it is not numeric. Let's check all of these, None of these. And here's how you can tell.
Because I selected a whole bunch of things that clearly should be numbers.
And down here in the status bar it's saying Count is 34.
It's not telling me the Numeric Count. It's not telling me the Total.
It's not telling me the Average. Clearly something is wrong.
And then I start to wonder. Okay, I copied this data from SEC Edgar Database.
I pasted to Excel. I mentioned yesterday I had to cleaned up some merged cells and things like that. But what is it?
Is it the comma? Are the commas causing the problem?
So what's the length? Let's see what the length is.
So we have number 12, comma 3, 6, 3. And the LEN comes back as seven.
And you and I think one, two, comma 3, 6 3, that should be six, right?
So there's something extra there. So my usual strategy here is we're just going to come in and say =MID of that number 12, 3, 6 3. And I want the MID starting at all seven numbers.
So the SEQUENCE of seven. For a length of one.
Enter. That's going to give me seven answers.
So I'm getting one, two, comma 3, 6, 3. And again, at this point I still think it's the comma is a weird comma. So what I'm going to do is ask for =CODE.
Give me the ASCII code for all the numbers. And that hash sign there is the array formula operator. And so I get a 49, which is a one - that checks out. Fifty.
And then I have to admit, I actually don't know.
If I just type a comma here. What should the CODE be for a comma?
44 - okay, so that's right. Fifty one, Fif… Oh gosh darn it. It's a character 160.
This is a non breaking space. This has been a problem ever since the web became a thing. If people need space, space, space to appear in a website. They're not putting spaces in there.
They are putting character 160 – it is the ampersand N B S P ;, Because the browser won't eliminate three non breaking spaces.
It'll leave them there and maybe things will be lined up a little bit.
It drives me crazy. And what the thing that really drives me crazy is we have a function called CLEAN And Excel is supposed to get rid of all the garbage. But CLEAN will not clean the character 160.
It's just so freaking annoying that this has been annoying us for so long.
How hard could it be for the Excel team, for the Calc team to fix CLEAN so that way it actually gets rid of the one thing that we needed to clean. Just horribly frustrating.
Okay, so here's what we're going to do. I'm going to select all of these cells that should be numeric here. And go into Find and Replace with Control H. And what I need to type up here in the “Find What” is a character 160 space. And unfortunately that's not on our keyboard.
So here's one way to do that. I'm going to hold down the Alt key on my keyboard and type Zero One Six Zero and then release the Alt.
Alright? Now, it's frustrating - it's a space - you can't see it. But you can see the insertion point moved over when I finish that. So hold down Alt.
While I'm holding down ALT. 0, 1, 6, 0 and then let go of Alt and it inserts a character 160 And I want to replace that with nothing.
So I leave that blank. Replace All.
Done. We made 74 replacements.
Okay? So yay, we now have numbers down here. But let's think about what happened in yesterday's video. That means that I sent Copilot a whole bunch of text numbers and they're starting to do math on the text and it worked.
And then I have this horrible feeling. What if the only reason that it worked is because it was text? What happens when we send numbers to Copilot?
Does that break it making yesterday's video completely wrong?
Let's fix these others here. The other two sections.
That's my panic, okay? So I'm going to save this with a new name.
That new name being Coca-Cola Financial statements as numbers.
And then I ran the exact same questions that I ran yesterday.
And it came up with the exact same answers. A weird bug here.
Where they're showing me the formatting codes with the actual numbers.
But if you go through there, they're actually there.
This bug was there last week. It was fixed on the weekend when I recorded the video for yesterday and now it's back. So this is all stuff in preview and I get it.
Things are going to break and then not break. So the message for today, if you're copying numbers from SEC Edgar and those numbers are showing up as text in Excel, it's very likely that it's a character 160. Remember that awesome trick, hold down Alt and then type 0, 1, 6, 0, and let go of Alt. That allows you to type a non breaking space in the Find And Replace box. So that we can convert those non breaking spaces to nothing. And get numbers back and continue your analysis.
Crazy. Alright, Hey, I want to thank you for stopping by. We'll see you next time for another net cast from MrExcel. Hey, just below the video, click this Store icon.
We have a whole bunch of new mugs and shirts professionally designed, all kinds of great fun slogans for you or your favorite Exceller. If you like these videos, please down below, Like, Subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
Learn Excel from MrExcel podcast, episode 2654 – Why are these SEC Edgar numbers all text in Excel?
Hey, we're going to take a sort of break from Copilot today to talk about this problem.
But first my apologies to the folks out there who are left-handed and hold their coffee mugs in the other hand. And you want to have that message showing to your coworkers. We now have left-handed or right-handed coffee mugs so the message can be out. Okay.
Yesterday's video, 2652 – Financial analysis using Copilot in Excel was mind blowing.
It was absolutely amazing. Again, thanks to Dr. Nitin and his channel for that. But we're in this strange period in history where copilot and other LLMs can solve things really quickly.
They're growing by leaps and bounds. But if you're in a situation where the numbers actually matter. You need to go back and verify that Copilot is correct. Which really, if you think about it, causes you to go to Copilot, get the answers, and then go verify all the answers.
It takes longer than just performing the analysis yourself.
Now that will change. It will get to the point where we can trust this. But right now, we're not at the point where we can trust it. So yesterday here was the financial statement.
Here's the answers that I got from Copilot. I copied these from Copilot.
Paste them into a text box here. And I'm going to go through and verify all of these before I post the video to make sure that it's actually working.
So Current Ratio, we need Current Assets divided by Current Liabilities.
So simple enough. Point to current assets.
Point to current liabilities. And I should have already known I was in trouble because they are left justified. What the heck is up with that?
And sure enough, when I divide this by that, I get a #VALUE! error.
What the heck? So =ISNUMBER and click on that cell and it is not numeric. Let's check all of these, None of these. And here's how you can tell.
Because I selected a whole bunch of things that clearly should be numbers.
And down here in the status bar it's saying Count is 34.
It's not telling me the Numeric Count. It's not telling me the Total.
It's not telling me the Average. Clearly something is wrong.
And then I start to wonder. Okay, I copied this data from SEC Edgar Database.
I pasted to Excel. I mentioned yesterday I had to cleaned up some merged cells and things like that. But what is it?
Is it the comma? Are the commas causing the problem?
So what's the length? Let's see what the length is.
So we have number 12, comma 3, 6, 3. And the LEN comes back as seven.
And you and I think one, two, comma 3, 6 3, that should be six, right?
So there's something extra there. So my usual strategy here is we're just going to come in and say =MID of that number 12, 3, 6 3. And I want the MID starting at all seven numbers.
So the SEQUENCE of seven. For a length of one.
Enter. That's going to give me seven answers.
So I'm getting one, two, comma 3, 6, 3. And again, at this point I still think it's the comma is a weird comma. So what I'm going to do is ask for =CODE.
Give me the ASCII code for all the numbers. And that hash sign there is the array formula operator. And so I get a 49, which is a one - that checks out. Fifty.
And then I have to admit, I actually don't know.
If I just type a comma here. What should the CODE be for a comma?
44 - okay, so that's right. Fifty one, Fif… Oh gosh darn it. It's a character 160.
This is a non breaking space. This has been a problem ever since the web became a thing. If people need space, space, space to appear in a website. They're not putting spaces in there.
They are putting character 160 – it is the ampersand N B S P ;, Because the browser won't eliminate three non breaking spaces.
It'll leave them there and maybe things will be lined up a little bit.
It drives me crazy. And what the thing that really drives me crazy is we have a function called CLEAN And Excel is supposed to get rid of all the garbage. But CLEAN will not clean the character 160.
It's just so freaking annoying that this has been annoying us for so long.
How hard could it be for the Excel team, for the Calc team to fix CLEAN so that way it actually gets rid of the one thing that we needed to clean. Just horribly frustrating.
Okay, so here's what we're going to do. I'm going to select all of these cells that should be numeric here. And go into Find and Replace with Control H. And what I need to type up here in the “Find What” is a character 160 space. And unfortunately that's not on our keyboard.
So here's one way to do that. I'm going to hold down the Alt key on my keyboard and type Zero One Six Zero and then release the Alt.
Alright? Now, it's frustrating - it's a space - you can't see it. But you can see the insertion point moved over when I finish that. So hold down Alt.
While I'm holding down ALT. 0, 1, 6, 0 and then let go of Alt and it inserts a character 160 And I want to replace that with nothing.
So I leave that blank. Replace All.
Done. We made 74 replacements.
Okay? So yay, we now have numbers down here. But let's think about what happened in yesterday's video. That means that I sent Copilot a whole bunch of text numbers and they're starting to do math on the text and it worked.
And then I have this horrible feeling. What if the only reason that it worked is because it was text? What happens when we send numbers to Copilot?
Does that break it making yesterday's video completely wrong?
Let's fix these others here. The other two sections.
That's my panic, okay? So I'm going to save this with a new name.
That new name being Coca-Cola Financial statements as numbers.
And then I ran the exact same questions that I ran yesterday.
And it came up with the exact same answers. A weird bug here.
Where they're showing me the formatting codes with the actual numbers.
But if you go through there, they're actually there.
This bug was there last week. It was fixed on the weekend when I recorded the video for yesterday and now it's back. So this is all stuff in preview and I get it.
Things are going to break and then not break. So the message for today, if you're copying numbers from SEC Edgar and those numbers are showing up as text in Excel, it's very likely that it's a character 160. Remember that awesome trick, hold down Alt and then type 0, 1, 6, 0, and let go of Alt. That allows you to type a non breaking space in the Find And Replace box. So that we can convert those non breaking spaces to nothing. And get numbers back and continue your analysis.
Crazy. Alright, Hey, I want to thank you for stopping by. We'll see you next time for another net cast from MrExcel. Hey, just below the video, click this Store icon.
We have a whole bunch of new mugs and shirts professionally designed, all kinds of great fun slogans for you or your favorite Exceller. If you like these videos, please down below, Like, Subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
