Removing the ' before numbers

G

giantwolf

Hi all,

Quick question for you. Does anyone know a way (using formula's, cell
settings or by way of macro) to remove the little ' that appear before
numbers in excel after exporting from databases? I would just delete
them but I have 30,000+!! They are hindering my ability to do lookups.
I cannot export differently from the database either as I do not have
the file.

Cheers,

GW
 
0

0-0 Wai Wai ^-^

Do this in this way:
- Press Ctrl+H
- In Find what: type [ ' ] (excluding the square brackets)
- In Replace with: just leave it COMPLETELY blank (don't even add [space] since
it is reluctant)

After all, the above are merely my little opinion/idea.
Since my ability is limited, I could be wrong.
 
G

giantwolf

philiphales said:
You could try 'find and replace'

therefore find ['] replace with [space]
Click to expand...

Thanks, I've tried that though. Sorry I should have mentioned tha
you can only see these little ' when you actually select a cell. The
do not appear when you are just normally viewing the spreadsheet.

ie: without selecting a cell you would see:

123456

however when you select that cell, in the viewer at the top of the pag
(above the column letters) you would see:

'123456

Anyone?

Cheers,

G
 
P

philiphales

I think this means that the cell is formatted as TEXT highlight the
whole column and change the formatting to GENERAL or NUMBER.

Hope this works
 
D

dominicb

Good morning Giantwolf

I have a free add-in available to anyone who requests it, whic
contains a number of utilities. One of these will do just what you'r
asking. If you want this, just drop me a line.

HTH

DominicB
[email protected]
 
J

Jim May

Sub nopreapostophe()
For Each c In Selection
If c.PrefixCharacter = "'" Then c.Value = c.Value
Next
End Sub

works best
HTH
 
R

Robert_Steel

If the lookup is having problems with numbers then the way to solve the
problem is to type a 1 in a spare cell
copy it.
then select all of the problem data and
PasteSpecial with values and multiply selected.

This will coerce all of the text into numbers and should solve the problem

The reason the find replace suggestions does not work is detailed in the
EXCEL help

#######################
The Find and Replace commands don't find data.

Hide special characters If an asterisk (*), caret (^), quotation mark
("), or backslash (\) appears in the formula bar but not in the worksheet
cells, the character is a formatting code. Because such characters are not
actual data, you cannot use the Find and Replace commands to locate them.
##########################

hth RES
 
Top