Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting extra spaces in a string
I have the following problem: I'm comparing two price tables to see if there are any numbers that do not match. One of the tables is a Excel table with the numbers written as "1000000" while the other table is an imported xml table which has numbers written as "1 000 000". These are actually spaces between the digits. They can not be erased by formating the cell from 'text' to 'number' or vice versa. Any suggestions? Thanks in advance. Eric ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting extra spaces in a string
Eric,
I assume you mean VBA as you are in the programming group, so Replace them replace(myString","") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Eric" wrote in message ... I have the following problem: I'm comparing two price tables to see if there are any numbers that do not match. One of the tables is a Excel table with the numbers written as "1000000" while the other table is an imported xml table which has numbers written as "1 000 000". These are actually spaces between the digits. They can not be erased by formating the cell from 'text' to 'number' or vice versa. Any suggestions? Thanks in advance. Eric ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting extra spaces in a string
select the second Table and do Edit=Replace
What space bar With leave blank -- Regards, Tom Ogilvy "Eric" wrote in message ... I have the following problem: I'm comparing two price tables to see if there are any numbers that do not match. One of the tables is a Excel table with the numbers written as "1000000" while the other table is an imported xml table which has numbers written as "1 000 000". These are actually spaces between the digits. They can not be erased by formating the cell from 'text' to 'number' or vice versa. Any suggestions? Thanks in advance. Eric ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting extra spaces in a string
This thread discusses a variety of plausible solutions.
http://www.google.com/groups?hl=en&l...b59c72&rnum=17 HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- I have the following problem: I'm comparing two price tables to see if there are any numbers that do not match. One of the tables is a Excel table with the numbers written as "1000000" while the other table is an imported xml table which has numbers written as "1 000 000". These are actually spaces between the digits. They can not be erased by formating the cell from 'text' to 'number' or vice versa. Any suggestions? Thanks in advance. Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting extra spaces in a string
I'm afraid it doesn't work.. I tried your suggestions (replace function)... ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting extra spaces in a string
maybe the replace function does not work because I use Excel 97? Couldn't get the substitute function working either, though.. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting extra spaces in a string
Eric,
Replace cam in in XL2000. Here's a custom Replace function written by Jan Karel Pieterse, which you can add to your project, it works the same Function MyReplace(sSource As String, _ sToReplaceWith As String, _ sToReplace As String) As String Dim lPos As Long Dim sTemp As String sTemp = sSource Do lPos = InStr(sTemp, sToReplace) sTemp = Left(sTemp, lPos - 1) & sToReplace & _ Right(sTemp, Len(sTemp) - Len(sToReplaceWith) - lPos + 1) Loop Until InStr(sTemp, sToReplace) = 0 MyReplace = sTemp End Function -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Eric" wrote in message ... maybe the replace function does not work because I use Excel 97? Couldn't get the substitute function working either, though.. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save as CSV: extra spaces around commas | Excel Discussion (Misc queries) | |||
removing extra spaces | Excel Discussion (Misc queries) | |||
eliminating extra spaces in Excel cells | Excel Discussion (Misc queries) | |||
Removing random extra spaces | Excel Discussion (Misc queries) | |||
Extra Spaces with copied data | Excel Discussion (Misc queries) |