Removing the ' before numbers
 

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


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=465846

You could try 'find and replace'

therefore find ['] replace with [space]


--
philiphales
------------------------------------------------------------------------
philiphales's Profile: http://www.excelforum.com/member.php...o&userid=18641
View this thread: http://www.excelforum.com/showthread...hreadid=465846

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.

philiphales Wrote:
You could try 'find and replace'

therefore find ['] replace with [space]

Thanks, I've tried that though. Sorry I should have mentioned that
you can only see these little ' when you actually select a cell. They
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 page
(above the column letters) you would see:

'123456

Anyone?

Cheers,

GW


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=465846

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


--
philiphales
------------------------------------------------------------------------
philiphales's Profile: http://www.excelforum.com/member.php...o&userid=18641
View this thread: http://www.excelforum.com/showthread...hreadid=465846

Good morning Giantwolf

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

HTH

DominicB



--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=465846

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

works best
HTH

"giantwolf" wrote
in message ...

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


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile:
http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=465846

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