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

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

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

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

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

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

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