Adding a ' to the front of a column of numbers

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
 
F

Frank Kabel

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
 
E

ElsiePOA

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
 
I

icestationzbra

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
 
G

Gord Dibben

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
 
Top