Inserting a text character after formula

**Wendus** · 02-01-2005, 09:52 PM

Please excuse this humble question from a total newbie ... how do I insert a single text character after a formula?

The formula works out a total, dependant on user input, but I need to put a text character immediately after the total, eg: 29A, 41C, etc.

Many thanks.

pinmaster · 02-02-2005, 08:52 AM

Hi
Use the & sign:
=your_formula&"A"

HTH
JG

**Wendus** · 02-02-2005, 09:37 AM

Hello

Thank you ever so much for your help. I've tried what you suggested, but although it does work, it also throws the calculation result out. The results are percentages, and the cells have been formatted so that the results are shown as whole numbers only. When I try to add a text figure immediately after the formula, the percentages revert to 'something-point-loads of numbers'.

Being a newbie (and a grey-haired one at that!), I've been trying to teach myself to use Excel, so I'm probably doing loads of things wrong, even though I'm managing to achieve the required results ... until now.

This one has got me stumped.

Wendy

**swatsp0p** · 02-02-2005, 10:02 AM

Wendus: whenever you add text to number, Excel treats the entire result as text. Hence, you will lose any number style formatting ($, %, Dates, etc.) You can, however, limit the number of characters displayed in your number result by using the Round function and force a display as % as such:

=ROUND(A1/A2,2)*100&"% A" so that if A1=27 and A2=47 the displayed result will be:

57% A

note: the ",2" in the formula says to only display 2 places past the decimal, this can be adjusted to meet your needs. e.g. ",3" would show 57.4% A as the result.

Hope This Helps (HTH)

**Wendus** · 02-02-2005, 05:11 PM

Bruce, I love you! Thank you so so much, everything works perfectly now!

Purely as a matter of interest (and no, this isn't what I want to do, it's just my mind racing on ...), if all the percentage results were in a row, and needed to be totalled in the final cell, could it be done, or would the text characters make the whole row 'unreadable' to a formula in the final cell?

Thank you again, your help is very much appreciated.

Wendy

**swatsp0p** · 02-03-2005, 10:31 AM

Wendy: You are welcome (and I love helping you)

As for totalling (summing) the column of results, what you ask IS possible, but requires using an ARRAY formula (which is entered by pressing CTRL+SHIFT+ENTER)

Assuming your newly calculated %'s are in column C (rows 1-4 in my example), in cell C5 enter this formula and use the key combination above to confirm (enter) it:

=SUM(VALUE(LEFT(C1:C4,2)))&"% A"

after you press CTRL+SHIFT+ENTER, the formula will look like this in the formula bar:

{=SUM(VALUE(LEFT(C1:C4,2)))&"% A"} note the braces that Excel places around the formula!

The result will be the sum of the numbers plus the addition of the % sign and the letter 'A' after the number. Also note the ,2 in the formula will look at the first 2 digits, if you have carried your number to decimals, adjust this to ,4 or more as needed (remember to count the decimal as one place, so 54.7% is four characters for the number!)

The main problem, of course, is if you need to use letters other than 'A', as each would need to manually be edited in each formula, and the sum formula would only return whatever letter you entered in the &"% _" portion.

Good Luck