Tech Tips & Consulting

Welcome to my new tech home!

I’m working hard at creating a place where it’s easy to find answers for what should be simple day to day tech tasks. Thanks for visiting & I look forward to your requests & hope to provide solid answers.

Office Tips

Ctrl+H Find & Replace

 Replace Carriage Returns

  • Use ^p to find and replace carriage returns

Useful Excel Formulas

Add leading 0’s to a number that has less than 4 characters

Example:

Cell A2 contains the number 5, we would like it to display 0005

Paste this formula in B2 cell =REPT(“0”,4-LEN(A2))&A2

B2 will now display 0005

 

Add 2 zero before 6 digit number ie.. 281234 would now be 00281234

=REPT(“0″,8-LEN(A4))&A4

 

Format 8 digit number with dashes is specified intervals ie.. 00281234 would now be 00-281-234

=TEXT(A4,”00-000-000”)

 

Removing last character “-” in a cell and not returning 0 in the empty cells

=IF(L3 > 0,IF(RIGHT(L3,1)=”-“,LEFT(L3,LEN(L3)-1),L3),””)

 

Removing first character “-” and capitalize the letter that comes after

=IF(LEFT(L3,1)=”-“,UPPER(MID(L3,2,1))&RIGHT(L3,LEN(L3)-2),L3)

 

Percentage Upcharge =B2+(B2*$C$1)

Part# Cost 10.00%
00-281-234 $2.00 $2.20

 

Quick Blank Cell Fill (You can quickly fill in all blank cells with the text you type)

Select your cell region or range

Press Ctrl-G to display the Go To dialog box

Click the Special button & Double-click on Blanks (The blank cells remain selected)

Type the text you desire to populate the blank cells & Press Ctrl-Enter (The text you typed will fill in all the selected cells at once)