Microsoft Office Q&A: General Discussion (Word, Excel, PowerPoint...)

The real issue here is how to properly split

p29542954:billy5106:110901867220
I try splitting by the colon (:), which gives me columns A, B, and C. Column B (billy5106) is useless to me, so I just delete it.
But then the headache starts because I can't get the result I want, which should be p29542954-110901867220
Instead, every single time I perform the split, I end up with this mess:
p29542954-1,10902E+11
That "E+" scientific notation is driving me insane. How do I make it stop???

Jonathan Harris9 said:The issue is how to separate

p29542954:billy5106:110901867220:billy5106:110901867220
I split them using ":" and get columns A, B, and C. Column B is useless to me, so I delete it.
But here's the snag—I can't get p29542954-110901867220
instead, every time I split it, I end up with
p29542954-1,10902E+11
That "E+" thing is driving me crazy... any fix???

Once you pick ":" as the delimiter, just set the format for columns A and C to Text.

Jonathan Harris9 said:The issue is I can't get p29542954-110901867220
I just keep getting p29542954-1,10902E+11 whenever I split it up

1. Use Text to Columns with the ":" delimiter and you'll have A, B, and C split up perfectly.
2. Format Column C as a Number.
3. In cell D1, just pop in this formula: =CONCATENATE(A1;"-";C1)
4. Delete Column B.
5. Boom. Now Column C shows your result: p29542954-10901867220

I'm not exactly an expert in this area, so I'm looking for some guidance if you don't mind:

In Excel, I want to have a list of companies using just their shortened names—for example, "Smith & Co"—and then have a button that, when clicked, pulls up the full profile, including the legal entity name, tax ID, and address.

Is this something that can be done, and would it be difficult to set up?

Thanks a million!

Larry Ward8 said:I want to have a list of companies in Excel—shortened names, like "John Doe"—and then, when I click a button, I want it to pull up the full details for that firm, you know, like "John Doe Corp," their Tax ID, and their business address.

Are you just looking to view this "expanded" data, or do you actually need to manipulate it somehow? Because, based on how you've described the workflow, I suspect you might be using the wrong tool for the job entirely—this sounds like a classic case where you really should be using Access.

Nicholas King said:Are you just looking at this "open" data, or do you actually need to perform tasks with it? Based on how you described the issue, it sounds like you might be using the wrong tool—this feels like a classic Access situation.

I work as a traveling sales rep for a small company, and to make manual invoicing easier, I’d love to have all my customers listed by their business names.
Then, when I click on one, I want their specific company details to pop up so I don't have to hunt for their Tax ID or address every single time.

Once I get that working, I’d try to link everything else together (all shipping receipts get scanned and entered into Excel by number and company name), so if possible, I could filter by customer to see their total billings. Essentially, a mini accounting program.
I am willing to set aside some budget just to make my life easier.

Thanks

Larry Ward8 said:basically just a small accounting program

Now we're talking. 🙂

You really have two paths here: you can look at what’s already available on the market and then figure out how to sync your data with a finished product—and honestly, just because your data is currently sitting in Excel doesn't mean everything *has* to stay in Excel—or, you can start from scratch and build something specifically to solve your exact headache.

The second option always sounds more appealing—because an "off-the-shelf" product can never be quite the perfect fit for your specific needs. But, there are a lot of "buts" involved in that logic. 🙂

Since you aren't exactly an expert in Excel / Access / whatever tool would be used to build this, you’d essentially have to find someone, explain your requirements, wait while they build it, then test it... Most importantly: during the development phase, you'll inevitably think of a million more ideas and a million tiny details—you can see for yourself how the initial simple explanation has already "grown," and I can already spot several potential future upgrades and automations: for instance, why manually printing? If there is so little work that you can just do it manually, then you don't even need a program in the first place; and so on; once it's "finished," you'll be completely dependent on that developer for every single tweak or upgrade. A custom-built product will certainly be much better and tailored exactly to your desires—but, it requires significant time and money. I'm not entirely sure if the "some money" you mentioned would actually be enough. 🙂

Disclaimer: I don't deal in these things (meaning, I'm not selling anything 🙂) — just a few things to chew on.

Larry Ward8 said:I’d want to pull in all my customers by name (business name), and when I click them, have it open up the specific company info needed for an INVOICE so I don't have to hunt down tax IDs and details every single time.

And if possible, let me filter by customer to see how many invoices they've had. Basically just a tiny accounting program.

On top of what @Nicholas King/">@@Nicholas King gave you to chew on, I’ll just add

The FIRST PART of the question:
Search Google for "Hyperlink in Excel to a Sheet" (hyperlinks)
Basically, on one Sheet labeled "HYPERLINKS," you have a list of all companies, and you'll have that many Sheets. Click the hyperlink, and boom—it automatically jumps you to that specific company's Sheet and their data.

The SECOND PART:
On a single "COMPANIES" Sheet, you have a table pulling data from all the other Sheets (companies).

The key is planning out all your parameters and how the data and Sheets are organized. That's if you're tackling this project yourself.
BTW: Check out how a "little project" can be designed for an Invoice—there's even a sample download there.

But look, if you're a total Excel newbie, don't get it, and you aren't exactly a wizard with formulas, just listen to @Nicholas King/">@@Nicholas King.

Simple question here:

HOW DO I JUST TURN EVERYTHING OFF IN MICROSOFT OFFICE!!!

Look, I’ll write a few sentences, drop in an image, set it as the background—you know the drill—but some damn software logic keeps moving my text and my images all over the page everywhere!!!!!! I just want everything to stay exactly where I put it manually without the program messing with my layout, you get me?

And another thing—I finish up a document in Word, try to save it as a PDF using Adobe Acrobat, but suddenly all my images turn this weird bluish color.

thanks

I need some help processing statistical data (school grades). I've put together a spreadsheet listing students and their subjects.
The subjects are split into general studies (blue) and science (brown), so I have two separate general averages, and their arithmetic mean gives me the final grade (gray and green). This works fine if all grades are positive, but if any grade is negative, then that entire section of the curriculum (general or science) needs to be marked as a 1, which means the final grade should also be negative.
What formula should I use??

Code:

If you're looking at a range like A1:A5 filled with grades, just swap out 🙂

I need some help processing statistical data (school grades). I've put together a spreadsheet listing students and their subjects.

The subjects are split into general coursework (blue) and science tracks (brown), so I have two different general averages, and their arithmetic mean gives the final grade (gray and green). This works fine if all grades are positive, but if any grade is negative, then that specific section (general or science) needs to be marked as 1, which means the final grade also needs to be negative.
What formula should I use??

Carl Castillo69 said:I’ll write a few sentences, toss in an image, try to set it behind the text, and—I don't know—everything just goes haywire! Some glitch keeps moving my text and images all over the damn page!!!!!! I just want everything to stay exactly where I put it manually, without it jumping around unless I actually move it myself, you know?

The thing is, in Word, an image gets "anchored" to a specific paragraph—it basically shares its fate—so if that paragraph shifts, the image moves right along with it. If you turn on the "Show/Hide ¶" tool (you know, the one that shows paragraph marks and other formatting symbols), select the image, and look near the end of a paragraph, you'll see a little anchor icon indicating where it's stuck.

If you’re looking to have a fixed image sitting in the background, honestly, you might be better off just inserting it into the Header.

And another thing—I'll make a document in Word and then use Adobe Acrobat to print it out as a PDF, but suddenly all my images turn some weird shade of blue.

Either you have the output set to grayscale, or maybe you should try using different software (like CutePDF, for instance).

Kyle Johnson2 said:Check out these step-by-step tutorials on the US site.

- How to start page numbering on page X in Word 2003
- So, you're trying to start page numbering in Word 2007 from somewhere other than page one? It’s a bit of a headache if you don't know the trick, but I got you. Basically, you can't just hit "insert" and hope for the best—it'll just number everything from the very beginning. You have to break the connection between the first few pages and the rest of the document. First, go to the bottom of the page right before where you want your numbers to actually start. Go to **Layout** > **Breaks** and select **Next Page**. This creates a Section Break. Now, go into the Footer of that new section, look for the option that says **Link to Previous** in the toolbar, and click it to turn it off. Once that's unlinked, you can go ahead and insert your page numbers, and they won't mess up your intro pages. It’s a little clunky, but it works. Good luck.

driftingnomad68 said:You gotta drop in a section break after page 5.

Check this out:
http://www.youtube.com/watch?v=E1WtKR6FDM4
Or just Google it. 🙂

Thanks a ton, everyone. You guys seriously helped me out big time.😁

Now I’ve got another problem on my hands.

That bit in the Table of Contents.
for example
1. Introduction......................................................1
2. Concept.......................................................................2

Anyone know how to align this properly so nothing keeps jumping around on me?🤷

Actually, that whole process is pretty much automated. You just make sure every title and subheader is assigned its own specific Heading style (like 1, 2, etc.), and then you can just pull it all together automatically. In Word 2007, you just head over to the References tab and grab the Table of Contents from the left side.

If you haven't been messing with the Heading styles yet, there’s also that other option right in that same menu—under the "Add Text" section—where you can manually tell the program what's what.

But hey, if you really want to get hands-on and do it the old-fashioned way, you can always just play around with the text alignment settings. 🙂

I’m looking for some help with Google Sheets, if anyone feels like lending a hand. I need a formula that can calculate how many hours fall within a specific window, based on a given time interval.

Here’s the scenario: if cell A1 shows 9:00 PM and B1 shows 11:00 AM—meaning I have a total elapsed time of 14 hours—I need a function that identifies only the portion of those hours that falls within a designated window, say 3:00 AM to 8:00 AM. In this specific case, the result should be 5 hours.

And naturally, if the duration specified in cells A1 and B1 doesn't overlap at all with my 3:00 AM–8:00 AM criteria, the formula should just spit out a zero or leave the cell blank.

I attempted to start something with =IF(PRODUCT({"03:00","08:00"}... 😵 but I quickly lost my way and ended up right back at square one.

Hopefully, I've made myself clear enough..

Joseph Chavez7 said:I hope I made myself clear enough..

Give this a shot:

=IF(A1>B1;MIN(4/3;B1+1)-MAX(1,125;A1);MAX(0;MIN(1/3;B1)-MAX(0,125;A1)))

In cell A1, you'll put the start time, and in B1, the end time (entered just like, say, 9:00 PM).

The formula doesn't quite hold up when you throw some extreme data at it—for instance, if someone works from 5:00 AM until 4:00 AM the next day.

Could use a little help here.
For example:
I have two sheets...
Cell A1 on Sheet 2 is linked
to cell A1 on Sheet 1.

Cell A1 on Sheet 1 is empty—just completely blank.
But cell A1 on Sheet 2 has this formula: =Sheet1!A1
Which means it just shows a 0 (zero).
I want to get rid of that zero. I want the cell to actually look "empty" instead of displaying a value.
Is there a way to make that happen?

restlessheron19 said:I really wish that zero wouldn't show up... basically, I want the formula to leave the cell blank instead of displaying a zero.
Is there actually a way to pull that off?

=IF(Sheet1!A1="", "", Sheet1!A1)

If you’re looking for a workaround—and if you don't want to mess with formulas—you can actually just go into the settings in Excel 2007 and toggle off the display for all zeros in cells via the Advanced Options. If you do that, you probably won't even need that IF statement.

Joseph Chavez7 said:Hope that makes sense..

Not really.
First you mention 9:00 PM to 11:00 AM, then 3:00 AM to 8:00 AM, then some "set criteria" without actually saying what the criteria are.

If you need formulas for subtracting time check out this tutorial.

For your specific example, if you want a formula for A1 and B1 (start-end) that calculates the duration regardless of whether it's day or night, use this:
Code:

e.g., START-END
03:00-08:00 will give you 5:00 hours
21:00-11:00 will give you 14:00 hours (handles crossing midnight)

If one of the cells is empty, the result stays blank (assuming that's what you meant by "criteria").