Adding a character to a column

  • Thread starter Thread starter Javi
  • Start date Start date
J

Javi

I have a column made up by numbers from 10000 to 30000, is there a way
to turn:

10000
10001
10002
10003
 
Javi

Not sure what you are asking for here.

Do you want to add 1 to each number?

What does "turn" mean in your question? Transpose from column to rows?

Gord Dibben Excel MVP
 
let me see if I can make it clear :o

I want it to stay in the same place.

if cell a1 was "10000" now I want it to read "n10000" (but stay in tha
same cell)
if a2 was "10001" now I want it to be "n10001"

n is just a letter that should appear in front of every number in tha
same column.

Did that hep
 
I'd just use a helper column:

="n"&a1
or
="n"&text(a1,"000000")

Then copy this helper column and Edit|paste special|values right over the
original column.

(Actually, I might keep the original column, too. Maybe just hide it--just in
case I ever needed it.)
 
Javi

In a helper column, perhaps column B?

In B1 enter ="n" & A1

Double-click on the fill handle(little black lump at bottom right corner) of
B1 to copy down column A.

When done, select column B and copy>paste special(in place)>values.

Delete column A.

If you can't use a helper column then you would be stuck with a macro.

Sub Add_Text_Left()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo Endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
moretext = InputBox("Enter your Text")
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Exit Sub
Endit:
MsgBox "only formulas in range"
End Sub

Gord
 
Back
Top