Adding a ' to the front of a column of numbers

  • Thread starter Thread starter punter
  • Start date Start date
P

punter

Hello everyone,

I have two columns of numbers in excess of 35,000 lines. I need to do
look up from to the other. The problem I'm running into is that th
first set of numbers has a ' in front it but the other one doesn't.
Example:

First column '123456
Second column 123456


I want to add the ' in front one column and I'm having some problems.
Concatenating isn't seeming to work. Any ideas?

Thank you,

Eddi
 
Hi
why not remove the ' from the other column. e.g. with a macro such as:

sub remove_it()
dim rng as range
set rng=selection
rng.value=rng.value
end sub
 
A couple of suggestions:

Rather than adding a ' to column 2, remove the ' from column 1 instead
using the Find/Replace function.

But, if you want to add a ' to column 2, insert a helper column an
enter: ="'"&B2. (The ''''' is actually a double quote, a single quot
and a double quote)

Then use copy and paste special/values to the helper column and remov
the original column
 
a solution with a different flavour...

if A1 has '123456, put the formula given below in B1.

=RIGHT(A1,LEN(A1)-1)

this will return 123456. as the other poster has suggested, its bette
to do away with the ' altogether than to put it in front of the number
that dont have them. that will protect the chastity of numbers :-)...

if you have your numbers in column A contiguously without any breaks i
between, then paste the formula in B1 and then use the autofill featur
to avoid having to drag the formula down over 35000 rows
 
Alternate

Copy an empty cell formatted to General.

Select your column with the '123456 numbers and Paste Special>Add>OK>Esc.

Gord Dibben Excel MVP
 
Back
Top