Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PAULP
 
Posts: n/a
Default HOW DO I ADD A LETTER BEFORE AN EXISTING NUMBER IN A CELL?

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default HOW DO I ADD A LETTER BEFORE AN EXISTING NUMBER IN A CELL?

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default HOW DO I ADD A LETTER BEFORE AN EXISTING NUMBER IN A CELL?

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



"PAULP" wrote:

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PAULP
 
Posts: n/a
Default HOW DO I ADD A LETTER BEFORE AN EXISTING NUMBER IN A CELL?

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!

"vezerid" wrote:

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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMay
 
Posts: n/a
Default HOW DO I ADD A LETTER BEFORE AN EXISTING NUMBER IN A CELL?

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.


"vezerid" wrote in message
ups.com...
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





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PAULP
 
Posts: n/a
Default HOW DO I ADD A LETTER BEFORE AN EXISTING NUMBER IN A CELL?

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

"JMay" wrote:

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.


"vezerid" wrote in message
ups.com...
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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default HOW DO I ADD A LETTER BEFORE AN EXISTING NUMBER IN A CELL?

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

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PAULP
 
Posts: n/a
Default HOW DO I ADD A LETTER BEFORE AN EXISTING NUMBER IN A CELL?

THANK YOU! WORKS PERFECTLY!

"vezerid" wrote:

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


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB
 
Posts: n/a
Default HOW DO I ADD A LETTER BEFORE AN EXISTING NUMBER IN A CELL?

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

"PAULP" wrote:

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

"JMay" wrote:

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.


"vezerid" wrote in message
ups.com...
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert row number into a cell? CR Optiker Excel Worksheet Functions 2 January 3rd 06 10:52 PM
Number of characters in a cell (one row, merged columns) Dajana Excel Discussion (Misc queries) 1 September 19th 05 09:30 PM
Convert Text (letter) To Number Excel reloadinternet Excel Worksheet Functions 2 August 22nd 05 03:49 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
How do I get a letter in one cell ito equal a number in anotherl.. Dave Cadey Excel Discussion (Misc queries) 4 February 25th 05 03:37 PM


All times are GMT +1. The time now is 12:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"