deleting extra spaces in a string

[Eric]

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

 

[Bob Phillips]

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)

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


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[Tom Ogilvy]

select the second Table and do Edit=>Replace
What space bar
With leave blank

--
Regards,
Tom Ogilvy

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


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[pfsardella]

This thread discusses a variety of plausible solutions.

http://www.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&th=b155a0a84b59c72&rnum=17

HTH
Paul

 

[Eric]

I'm afraid it doesn't work.. I tried your suggestions (replace
function)...

 

[Eric]

maybe the replace function does not work because I use Excel 97?
Couldn't get the substitute function working either, though..

 

[Bob Phillips]

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)

maybe the replace function does not work because I use Excel 97?
Couldn't get the substitute function working either, though..


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements