HOW DO I ADD A LETTER BEFORE AN EXISTING NUMBER IN A CELL?

[Guest]

I WANT TO INSERT A LETTER IN FRONT OF A NUMBERS THAT ALREADY EXIST IN CELLS
IN A COLUMN. SORT OF LIKE USING "FIND & REPLACE" EXCEPT THAT I DON'T HAVE
ANYTHING TO REPLACE; I JUST WANT TO INSERT A LETTER PREFIX IN FRONT OF
NUMBERS.

 

[vezerid]

In a cell next to your number (say A1 has the number, so in B1),

="A"&A1 (or whatever letter you want inside the quotes)

If you want to get rid of the formulas, select the column, Edit|Copy
and then Edit|Paste Special... checking Values.

HTH
Kostis Vezerides

 

[Guest]

You can give the appearance of same for display purposes, by just
re-formatting the number cells with Custom format A####

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

THIS SOUNDS LIKE IT MAKES SENSE & SHOULD WORK, BUT WHEN I TRY IT THE CELL
ONLY SHOWS THE FORMULA I INPUT (EG. IN B2, ="W"&A2) INTO THE CELL & DOESN'T
PERFORM THE FUNCTION.
THANX!

 

[JMay]

FWIW:
With the value 123 in Cell A1 - You can do a Format, Custom and in the box
type in "A"00000
and you'll get A00123 displayed.

 

[Guest]

THE PROBLEM IS THAT I NEED TO PERFORM THIS ACTION ON 30,000 CELL ENTRIES.
THANX!

 

[vezerid]

If the cell displays the formula there are two things to check:
1. The number format of the cell w/ the formula. Format|Cells...|Number
tab. If it is Text, then this explains why you are seeing the formula.
If so, select the entire column (by clicking the column header) and set
the format to General.
2. Tools|Options|View and see if the Formulas checkbox is checked. If
so, uncheck.

This is what you should do if you want to do it with formulas. Beware,
the suggested formula can be copied as far down as necessary. If you
have it in an adjacent column then, select B1 and bring the mouse
pointer over the bottom-right corner of the selection border, where you
see the Autofill handle. By double-clicking it will be automatically
copied as far down as necessary.

The difference between the formula and the formatting technique:
The formula will generate a *new value* and it needs an additional
column first. If you format it as suggested, the value is not changed
and it simply *displays* the character at the beginning. I.e. you can
still use a formula like =2*A1 and it will give you twice the number
part.

You choose what is best for you.

HTH
Kostis Vezerides

 

[Guest]

THANK YOU! WORKS PERFECTLY!

 

[Guest]

Select all of the cells you want formatted, then perform the formatting.