Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing dashes from phone numbers | Excel Discussion (Misc queries) | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) | |||
Removing numbers from the beginning of a text string | Excel Worksheet Functions | |||
Removing house numbers from addresses | Excel Worksheet Functions | |||
removing firsts two numbers from a list of #s | Excel Worksheet Functions |