Free Microsoft Excel 2013 Quick Reference

Show alternate or different characters

I have a column that contains Social Security numbers. I would like this
column to display the numbers as 'xxx-xx-1234'. I can't seem to be able to
figure out how to do this. Cell formatting doesn't tell really say about a
'replacement' character.

Post your answer or comment

comments powered by Disqus
I need to be able to click on one cell (that contains a description) and have it link to another cell that contains another 2 or 3 character value so that it will put the answer into another cell which I already have set up. I'm building a part number (that is mostly text) from multiple choices in a field of choices. It works now but I have to choose from the cells beside the descriptions. I would rather be able to choose the descriptions and get the text that resides in the cells beside them.

I'm trying to find a formula that will return True/False if the 2nd or third character in a cell is a number.

W123 would return true
SW123 would return true
Cafeteria would return false
ENE123 would return false


I'm making a worksheet for my company. I want to subtract one number
number from another number. I use the 'sum' format for this
'=sum(BL4-BP4) '. This format works fine for me in most cases, if
BL4 >BP4. However, if BL4

I'm doing a report with monthly averages for each team. What I want is when
an average number is met at A2,... A3, ... to show "yes" or "no" something
like this:
A2 A3
123 yes
95 no
150 yes

Dear Sir,

I have a problem using Vlookup formula .

The formula does not show blanks for items that are not found in the look up

Lets take the following workheet example :

Item Price
1 5110 25
2 5111 25
3 5112 25
4 5113 30
5 5114 30
6 5115 36
7 5116 36

Look up table
Item Price
20 5110 25
21 5113 30
22 5115 36
23 5120 40

When I enter =VLOOKUP (A1,A$20:B$23, 2) at B1 and copy down to B7, I always
get the answer as illustrated.

The items that are not found in the look up table has the value of the last
item that is found in the in the table.

The formula does not show blank or N/A (not available).

Is there any other way or other formula for getting what I want ?




I am just going nuts over this. I don't even know what to Google for this. What I am trying to do is have Vlookup show 0 or be blank if the criteria has been met before.

If it doesn't make sense, I'll tell why I am doing this. I have a table with duplicate data. I am trying to perform a vlookup based on that. It looks something like this:

Part, Comp, Quantity
A, 1, vlookup
A, 2, vlookup
A, 3, vlookup
B, 1, vlookup
C, 1, vlookup
C, 2, vlookup

Now that you see why I have duplicate data, the vlookup function looks for the "comp"

All I want to do is have vlookup skip Comp 1 if it has already been looked up once.

Any help please?

I wish to import data from one workbook colum (B) to another workbook colum
(A) the data is in alpha numeric eg:SY-50185-k-02. I dont want to affect the
existing data I am importing to, but I want to update any data that is new or
different to the existing.

I need help trying to find out the value from multiple criteria.
I've attached "sample". I need to show all the different outcomes (sum) from each category. Need help


In a column I have a list with different characters in an order I don't know.
How can I find out how many different characters I have?
In the attached example the result should be 3 (There are A's, B's and C's. But no D's, no E's and so on).

Thanks for your help,

I'm making a worksheet for my company. I want to subtract one number number from another number. I use the 'sum' format for this '=sum(BL4-BP4) '. This format works fine for me in most cases, if BL4 >BP4. However, if BL4<BP4, the sum that is shown is a negative number. I would like for the worksheet to show only positive numbers. I would like for the cell to show ' 0 ' , or nothing at all, if the sum is a negative number. For instance, 10-8=12 is ok, but 8-10=-2 is not. How do I do this? Thanks

I have a daily log of entry number for 3 different stages, each stage has enter and end type.
As attached, I have enter_game & end_gameenter_match & end_matchstart_round & end_round
Now i want to find a smart way to use pivot to show 3 END value as
% difference from 3 ENTER/START value. They should be 3 percentage like below(enter_game - end_game) / enter_game (%)(enter_match - end_match) / enter_match (%)(start_round - end_round) / start_round (%)
I just wonder if I can do it with pivot without creating new column manually.
Under value field settings, i chose 'show value as % difference from", base field is "type", base item is "previous" and I got it right with some glitches.

Glitch one, the % is negative, it basically use formula of (end_game - enter_game) / enter_game.
Glitch two, it show 3 unnecessary calculation in between different stages, so i get unwanted series like (enter_match - end_game) / end_game.

I hope to find a way to fix it and get exactly what i want.

In short, what I want to do can be done easily by manually adding a new source column for each difference and draw a new chart.
But I hope there is smarter way and pivot to do it for me.

I would like to display in a cell the word "GOLD" followed by a fancy arrow such as CHAR(228). Since each is from a different character set I don't get the results I need. How do I deal with two different character sets in the same text expression?

Hello Friends,

I am currently working on developing a VBA component that would read an excel sheet and determine if it has any Chinese or Japanese Characters in it.

I am completely unaware as to how this can be achieved. Any ideas will be greatly appreciated.

Thanks & Regards

can anybody please help me find a solution to fix all my damaged excel files? i opened them one day and all became unreadable, all the text contents are garbled and shows up with different characters. I tried to fix them with excelfix and excelrecovery but didn't work. Is there any other way to fix it via software? Is anybody kind enough to fix them for me? i scanned them all with norton but they are virus free... i am clueless why they got damaged.


I am using a User Form to input data to a spreadsheet and have an issue. I've set the TextBox WordWrap & MultiLine Property to True and the textbox values don't show the reverse P character, but how do I get the "square" from populating into the cells on my worksheet?
I was using this old thread as a point of reference, but didn't understand how to use or where to put it in my form. I'm referring to the code that Dave Hawley supplied.

Thank you in advance for you help,


I have a worksheet that contains carriage returns embedded in a cell. When I
open the sheet, I do not see these carriage returns, but my co-worker opens
the same sheet, he sees these carriage returns as control characters. There
is an option in Excel 2003 that you can set to show/hide these control
characters using the following method
Tools -> Options -> Internations tab -> Show/Hide control characters.

I checked the whole Excel 2007 to accomplish the same task and couldn't find
anything of this sort.

Can you please let me know how I can accomplish this issue. Hope this is not
a bug in Excel 2007.


I am importing text from two different HTML tables. Table One is Golf Scores
with the player Name and their position in the tournament. Table Two (from a
different website) is the World Golf Rankings, with the Player Name, and
their World Ranking.

I need to do Lookups in both tables using the name from Table One. When I do
a Lookup on Table Two using the name from Table One, it cannot locate it,
even though it is there.

A simple test seeing if the two seemingly identical text fields are "equal"
to each other, shows they are not.

Is tjhere some type of invisible formatting, or invisible characters that
are embedded in the text? And if so, is there a way to "normalize" the text?


If I have 2 cells containing us currency values and want to compare them and report the difference in another cell showing the value as a positive or negative value, what might be the proper formula. example: a calculated value in D2 of $6.10 and E2=$6.00 would show -$0.10 in F2
Conversely if D2=$6.00 and E2=$6.10 then F2 would show +$0.10.
Any help appreciated.


I have a column of filenames and would like to

1) produce a corresponding column with a varying number of characters trimmed from the end given a specific rule
2) produce another column showing a specific character in the filename on the condition that the character appears

Examples of filenames

In one column i want the filenames to be reduced by either 4 or 6 characters from the right depending upon if they end '.pdf' or '+X.pdf'

In another column i would like the character immediately following the '+' to appear. If there is no '+', then nothing should be returned. This would look like


The filenames will always be of varying length but the final 4 characters will always be '.pdf. and if a '+' appears the final 6 digits will always be '+X.pdf' with X being variable.

Have tried using search, find, if-else formulas but can't seem to merge them correctly to get the desired results. Am using excel 2003.

Any help would be much appreciated. Thankyou.

using excel 97 on winxp - suddenly the formula bar is only showing the last two and sometimes three characters of any particular cell. for instance if i type 'marc' hit enter and go back and highlight the cell the formula bar on shows 'rc'. if i type '12345' hit enter and highlight the cell it shows '45'. this happens on both data and formulas and in existing and newly created worksheets.

the issue does effect calculations and i can still edit the cell or formula in the cell.

thanks in advance.

I make two manual entries of average times to compare last year with this
year. I need to calculate the difference between the two times (it could be
an increase or decrease), and show both the difference (+ or -) in hours and
minutes, and the % of change (+ or -) from last year to this year.

Example: 2:03 average talk time last year, 3:50 average talk time this year
= increase of 1:47 (1 min. 47 sec.). or 86.99 % increase in talk time.


Hello Everyone,

I have been asked to show the date difference in terms of percentage i.e. 100%, 96% etc in excel. The benchmark I am using is, same day work will be treated as 100% accuracy i.e.(same date).On the other hand, if the date exceeds with 1 or 2 days,the accuracy won't remain 100% as compared to the benchmark and hence should be shown in excel field. Please help me in this regard as this is my first major assignment. Thanks in advance.


I read in another post how to show time in 12hr mode without showing AM or PM, but in that method it would show a small p for PM. Im looking for a way to show 12hr mode but NOT show the AM PM without having the time show in 24hr mode?

So if it is 1:00AM I need to see 1:00
if it is 1:00PM I need to see 1:00

Thanks in advance

Greetings! I have a series of nested If statements, and for each test, a different character string will show in the cell. I would like to have the character strings show in a cell other than the one containing the formula.
The formula is


No luck finding an answer? You could always try Google.